调试数据库功能

给数据库函数加上日志和错误处理，这样你就能在运行时看到它的行为。在复杂的函数中，日志最重要。

🌐 Add logs and error handling to a database function so you can see what it does at runtime. Logs matter most in a complex function.

关于如何编写和调用函数，请参见数据库函数。

🌐 For how to write and call a function, see Database functions.

值得记录的好目标包括：

🌐 Good targets to log include:

（非敏感）变量的数值
查询返回的结果

常规日志 #

🌐 General logging

在仪表板中使用 raise 关键字将自定义日志写入 Postgres 日志。默认显示三种严重级别：

🌐 Use the raise keyword to write custom logs to the Postgres logs in the Dashboard. Three severity levels appear by default:

log
warning
exception（错误级别）

1
create function logging_example(
2
  log_message text,
3
  warning_message text,
4
  error_message text
5
)
6
returns void
7
language plpgsql
8
as $$
9
begin
10
  raise log 'logging message: %', log_message;
11
  raise warning 'logging warning: %', warning_message;
12
13
  -- immediately ends function and reverts transaction
14
  raise exception 'logging error: %', error_message;
15
end;
16
$$;
17
18
select logging_example('LOGGED MESSAGE', 'WARNING MESSAGE', 'ERROR MESSAGE');

错误处理 #

🌐 Error handling

你可以用 raise exception 关键字创建自定义错误。

🌐 You can create custom errors with the raise exception keywords.

一个常见的模式是在变量不满足条件时抛出错误：

🌐 A common pattern is to throw an error when a variable doesn't meet a condition:

1
create or replace function error_if_null(some_val text)
2
returns text
3
language plpgsql
4
as $$
5
begin
6
  -- error if some_val is null
7
  if some_val is null then
8
    raise exception 'some_val should not be NULL';
9
  end if;
10
  -- return some_val if it is not null
11
  return some_val;
12
end;
13
$$;
14
15
select error_if_null(null);

数值检查很常见，所以 Postgres 提供了 assert 关键字作为简写。它的格式如下：

🌐 Value checking is common, so Postgres provides the assert keyword as a shorthand. It takes the following format:

1
assert <some condition>, 'message';

例如：

🌐 For example:

1
-- assumes an attendance_table with an id uuid column and a student text column
2
create function assert_example(name text)
3
returns uuid
4
language plpgsql
5
as $$
6
declare
7
  student_id uuid;
8
begin
9
  -- save a user's id into the user_id variable
10
  select
11
    id into student_id
12
  from attendance_table
13
  where student = name;
14
15
  -- throw an error if the student_id is null
16
  assert student_id is not null, 'assert_example() ERROR: student not found';
17
18
  -- otherwise, return the user's id
19
  return student_id;
20
end;
21
$$;
22
23
select assert_example('Harry Potter');

你也可以使用 exception 关键字捕获并修改错误信息：

🌐 You can also capture and modify an error message with the exception keyword:

1
create function error_example()
2
returns void
3
language plpgsql
4
as $$
5
begin
6
  -- fails: cannot read from nonexistent table
7
  select * from table_that_does_not_exist;
8
9
  exception
10
      when others then
11
          raise exception 'An error occurred in function <function name>: %', sqlerrm;
12
end;
13
$$;

高级日志记录 #

🌐 Advanced logging

对于更复杂的函数，或更难调试的情况，记录以下内容：

🌐 For a more complex function, or for harder debugging, log the following:

格式化变量
单独的行
函数调用的开始和结束

1
-- assumes a some_table with a col_1 int column and a col_2 text column
2
create or replace function advanced_example(num int default 10)
3
returns text
4
language plpgsql
5
as $$
6
declare
7
    var1 int := 20;
8
    var2 text;
9
begin
10
    -- Logging start of function
11
    raise log 'logging start of function call: (%)', (select now());
12
13
    -- Logging a variable from a SELECT query
14
    select
15
      col_1 into var1
16
    from some_table
17
    limit 1;
18
    raise log 'logging a variable (%)', var1;
19
20
    -- It is also possible to avoid using variables, by returning the values of your query to the log
21
    raise log 'logging a query with a single return value(%)', (select col_1 from some_table limit 1);
22
23
    -- If necessary, you can even log an entire row as JSON
24
    raise log 'logging an entire row as JSON (%)', (select to_jsonb(some_table.*) from some_table limit 1);
25
26
    -- When using INSERT or UPDATE, the new value(s) can be returned
27
    -- into a variable.
28
    -- When using DELETE, the deleted value(s) can be returned.
29
    -- All three operations use "RETURNING value(s) INTO variable(s)" syntax
30
    insert into some_table (col_2)
31
    values ('new val')
32
    returning col_2 into var2;
33
34
    raise log 'logging a value from an INSERT (%)', var2;
35
36
    return var1 || ',' || var2;
37
exception
38
    -- Handle exceptions here if needed
39
    when others then
40
        raise exception 'An error occurred in function <advanced_example>: %', sqlerrm;
41
end;
42
$$;
43
44
select advanced_example();