Skip to content
Database

Debugging database functions

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:

  • Values of (non-sensitive) variables
  • Returned results from queries

General logging#

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

  • log
  • warning
  • exception (error level)
create function logging_example(
  log_message text,
  warning_message text,
  error_message text
)
returns void
language plpgsql
as $$
begin
  raise log 'logging message: %', log_message;
  raise warning 'logging warning: %', warning_message;
  -- immediately ends function and reverts transaction
  raise exception 'logging error: %', error_message;
end;
$$;
select logging_example('LOGGED MESSAGE', 'WARNING MESSAGE', 'ERROR MESSAGE');

Error handling#

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:

create or replace function error_if_null(some_val text)
returns text
language plpgsql
as $$
begin
  -- error if some_val is null
  if some_val is null then
    raise exception 'some_val should not be NULL';
  end if;
  -- return some_val if it is not null
  return some_val;
end;
$$;
select error_if_null(null);

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

assert <some condition>, 'message';

For example:

-- assumes an attendance_table with an id uuid column and a student text column
create function assert_example(name text)
returns uuid
language plpgsql
as $$
declare
  student_id uuid;
begin
  -- save a user's id into the user_id variable
  select
    id into student_id
  from attendance_table
  where student = name;
  -- throw an error if the student_id is null
  assert student_id is not null, 'assert_example() ERROR: student not found';
  -- otherwise, return the user's id
  return student_id;
end;
$$;
select assert_example('Harry Potter');

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

create function error_example()
returns void
language plpgsql
as $$
begin
  -- fails: cannot read from nonexistent table
  select * from table_that_does_not_exist;
  exception
      when others then
          raise exception 'An error occurred in function <function name>: %', sqlerrm;
end;
$$;

Advanced logging#

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

  • Formatted variables
  • Individual rows
  • Start and end of function calls
-- assumes a some_table with a col_1 int column and a col_2 text column
create or replace function advanced_example(num int default 10)
returns text
language plpgsql
as $$
declare
    var1 int := 20;
    var2 text;
begin
    -- Logging start of function
    raise log 'logging start of function call: (%)', (select now());
    -- Logging a variable from a SELECT query
    select
      col_1 into var1
    from some_table
    limit 1;
    raise log 'logging a variable (%)', var1;
    -- It is also possible to avoid using variables, by returning the values of your query to the log
    raise log 'logging a query with a single return value(%)', (select col_1 from some_table limit 1);
    -- If necessary, you can even log an entire row as JSON
    raise log 'logging an entire row as JSON (%)', (select to_jsonb(some_table.*) from some_table limit 1);
    -- When using INSERT or UPDATE, the new value(s) can be returned
    -- into a variable.
    -- When using DELETE, the deleted value(s) can be returned.
    -- All three operations use "RETURNING value(s) INTO variable(s)" syntax
    insert into some_table (col_2)
    values ('new val')
    returning col_2 into var2;
    raise log 'logging a value from an INSERT (%)', var2;
    return var1 || ',' || var2;
exception
    -- Handle exceptions here if needed
    when others then
        raise exception 'An error occurred in function <advanced_example>: %', sqlerrm;
end;
$$;
select advanced_example();