# Debugging database functions

Logging and error handling inside Postgres 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](https://supabase.com/docs/guides/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](https://supabase.com/dashboard/project/_/logs/postgres-logs) in the Dashboard. Three severity levels appear by default:

- `log`
- `warning`
- `exception` (error level)

```sql
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:

```sql
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:

```text
assert <some condition>, 'message';
```

For example:

```sql
-- 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:

```sql
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

```sql
-- 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();
```