database functions

42 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.

Function level statement timeout

Set custom timeout for a specific database function using the SET clause in the function definition. Example: create or replace function myfunc() returns void as $$ select pg_sleep(3); $$ language sql set statement_timeout TO '4s'; This works with the Database REST API when called from Supabase client libraries and is useful for recurring functions that need special timeout exemptions.

Trigger basic structure with CREATE TRIGGER

Creating a trigger in Postgres requires two parts: a trigger function and a trigger object. The basic syntax is: create trigger "trigger_name" after insert on "table_name" for each row execute function trigger_function();

Trigger function definition in PL/pgSQL

A trigger function must return type trigger and is written in PL/pgSQL. Example: create function update_salary_log() returns trigger language plpgsql as $$ begin insert into salary_log(employee_id, old_salary, new_salary) values (new.id, old.salary, new.salary); return new; end; $$;

Trigger special variables: TG_NAME, TG_WHEN, TG_OP

Trigger functions have access to special variables: TG_NAME (trigger name being fired), TG_WHEN (BEFORE or AFTER), TG_OP (INSERT, UPDATE, DELETE, or TRUNCATE).

Trigger special variables: OLD and NEW record variables

OLD is a record variable holding the old row's data in UPDATE and DELETE triggers. NEW is a record variable holding the new row's data in UPDATE and INSERT triggers.

Trigger special variables: TG_LEVEL, TG_RELID, TG_TABLE_NAME, TG_TABLE_SCHEMA

TG_LEVEL indicates the trigger level (ROW or STATEMENT). TG_RELID is the object ID of the table on which the trigger is fired. TG_TABLE_NAME is the name of the table on which the trigger is fired. TG_TABLE_SCHEMA is the schema of the table on which the trigger is fired.

Trigger special variables: TG_ARGV and TG_NARGS

TG_ARGV is an array of string arguments provided when creating the trigger. TG_NARGS is the number of arguments in the TG_ARGV array.

BEFORE trigger executes before the triggering event

A BEFORE trigger executes before the triggering event occurs. Example: create trigger before_insert_trigger before insert on orders for each row execute function before_insert_function();

AFTER trigger executes after the triggering event

An AFTER trigger executes after the triggering event occurs. Example: create trigger after_delete_trigger after delete on customers for each row execute function after_delete_function();

FOR EACH ROW trigger execution

The 'for each row' clause specifies that the trigger function should be executed once for each affected row.

FOR EACH STATEMENT trigger execution

The 'for each statement' clause specifies that the trigger is executed once for the entire operation. This can be more efficient than 'for each row' when dealing with multiple rows affected by a single SQL statement, as it allows performing calculations or updates on groups of rows at once.

Drop trigger syntax

To delete a trigger, use: drop trigger "trigger_name" on "table_name";

Drop trigger in restricted schema with CASCADE

If a trigger is in a restricted schema, drop the function it depends on instead using the CASCADE clause to automatically remove all triggers that call it: drop function if exists restricted_schema.function_name() cascade; Make sure to take a backup of the function before removing it in case you need to recreate it later.

Complete trigger example for salary logging

Here is a complete working example that updates salary_log whenever an employee's salary is updated: create function update_salary_log() returns trigger language plpgsql as $$ begin insert into salary_log(employee_id, old_salary, new_salary) values (new.id, old.salary, new.salary); return new; end; $$; create trigger salary_update_trigger after update on employees for each row execute function update_salary_log();

Triggers automatically execute SQL on table events

Postgres triggers automatically execute a set of actions on table events such as INSERTs, UPDATEs, DELETEs, or TRUNCATE operations.

Webhook local development troubleshooting

If experiencing connection issues with webhooks locally, verify you are using the correct hostname (host.docker.internal or your machine's local IP address) instead of localhost.

Database Webhooks overview

Database Webhooks allow you to send real-time data from your database to another system whenever a table event occurs. You can hook into three table events: INSERT, UPDATE, and DELETE. All events are fired after a database row is changed.

Webhooks are wrappers around pg_net triggers

Database Webhooks are a convenience wrapper around triggers using the pg_net extension. The pg_net extension is asynchronous and will not block your database changes for long-running network requests.

Database Webhook creation steps

To create a Database Webhook: (1) Create a new Database Webhook in the Dashboard, (2) Give your Webhook a name, (3) Select the table you want to hook into, (4) Select one or more events (table inserts, updates, or deletes) you want to hook into.

Create webhook with SQL trigger

Webhooks can be created directly from SQL using a trigger statement that calls the supabase_functions.http_request function. The syntax is: create trigger "webhook_name" after insert on "schema"."table_name" for each row execute function "supabase_functions"."http_request"('url', 'METHOD', '{"Content-Type":"application/json"}', '{}', 'timeout');

Webhook HTTP methods

Webhooks currently support HTTP webhooks that can be sent as POST or GET requests with a JSON payload.

INSERT webhook payload type

The INSERT webhook payload has the following structure: type (string 'INSERT'), table (string), schema (string), record (TableRecord), old_record (null).

UPDATE webhook payload type

The UPDATE webhook payload has the following structure: type (string 'UPDATE'), table (string), schema (string), record (TableRecord), old_record (TableRecord).

DELETE webhook payload type

The DELETE webhook payload has the following structure: type (string 'DELETE'), table (string), schema (string), record (null), old_record (TableRecord).

Local Supabase webhook URL for Docker

When using Database Webhooks on a local Supabase instance, the Postgres database runs inside a Docker container. To target services running on your host machine from within the container, use host.docker.internal instead of localhost or 127.0.0.1. If that doesn't work, use your machine's local IP address instead.

Local webhook URL for edge functions

When triggering an edge function from a webhook in local Supabase development, the webhook URL would be: http://host.docker.internal:54321/functions/v1/my-function-name

Webhook logging and monitoring

Logging history of webhook calls is available under the net schema of your database.

Basic Postgres function syntax structure

A basic Postgres function consists of: (1) function declaration with `create or replace function name()`, (2) `returns type` clause specifying return type, (3) `language sql` or `language plpgsql` clause, (4) function wrapper with `as $$` opening and `$$;` closing, (5) function body with SQL statements. The final select statement in a function body will be returned if there are no statements following it.

Function naming constraints

Function names must be unique within a schema. Overloaded functions are not supported in Supabase.

Basic function returning scalar value example

Example of a basic function that returns a string: ```sql create or replace function hello_world() returns text language sql as $$ select 'hello world'; $$; ``` Execute with SQL: `select hello_world();` or via JavaScript SDK: `const { data, error } = await supabase.rpc('hello_world')`

Function returning table data with setof

A function can return a complete table set using `returns setof table_name` clause. This allows the function result to be filtered and selected like a normal table query. Example: `create or replace function get_planets() returns setof planets language sql as $$ select * from planets; $$;` can be queried as `select * from get_planets() where id = 1;`

Restrict function execution by revoking execute permissions

To restrict a specific function on a case-by-case basis, revoke execute permissions from both `public` and specific roles: ```sql revoke execute on function public.hello_world from public; revoke execute on function public.hello_world from anon; ``` To restrict all existing functions in a schema from specific roles: ```sql revoke execute on all functions in schema public from public; revoke execute on all functions in schema public from anon, authenticated; ```

Grant function execution to specific roles

Grant execute permissions on a function to a specific role using: `grant execute on function public.function_name to role_name;` Example: `grant execute on function public.hello_world to authenticated;`

Set default function privileges for new functions

To restrict execution permissions for all new functions, change the default privileges: ```sql alter default privileges in schema public revoke execute on functions from public; alter default privileges in schema public revoke execute on functions from anon, authenticated; ``` Then selectively grant permissions to specific roles as needed.

Logging in Postgres functions using raise keyword

Add logs to functions using the `raise` keyword with severity levels: `log`, `warning`, or `exception` (error level). Logs appear in the Dashboard's Postgres Logs. Example: ```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; raise exception 'logging error: %', error_message; end; $$; ``` Note: `raise exception` immediately ends the function and reverts the transaction.

Error handling in functions with raise exception

Create custom errors in functions using `raise exception` keyword. Example of throwing an error conditionally: ```sql create or replace function error_if_null(some_val text) returns text language plpgsql as $$ begin if some_val is null then raise exception 'some_val should not be NULL'; end if; return some_val; end; $$; ```

Using assert keyword in functions for value checking

Postgres provides the `assert` keyword as a shorthand for throwing errors when conditions are false. Syntax: `assert <condition>, 'error message';` Example: ```sql create function assert_example(name text) returns uuid language plpgsql as $$ declare student_id uuid; begin select id into student_id from attendance_table where student = name; assert student_id is not null, 'assert_example() ERROR: student not found'; return student_id; end; $$; ```

Exception handling with sqlerrm in functions

Capture and modify error messages using the `exception` keyword with `sqlerrm` (which contains the error message). Example: ```sql create function error_example() returns void language plpgsql as $$ begin select * from table_that_does_not_exist; exception when others then raise exception 'An error occurred in function <function name>: %', sqlerrm; end; $$; ```

Advanced logging patterns in functions

For complex functions, log formatted variables, individual rows, and function call boundaries. Use `raise log` with query results, variable values, and JSON conversions: ```sql -- Log function start raise log 'logging start of function call: (%)', (select now()); -- Log a variable from SELECT select col_1 into var1 from some_table limit 1; raise log 'logging a variable (%)', var1; -- Log query result directly without variable raise log 'logging a query with a single return value(%)', (select col_1 from some_table limit 1); -- Log entire row as JSON raise log 'logging an entire row as JSON (%)', (select to_jsonb(some_table.*) from some_table limit 1); -- Log values from INSERT/UPDATE/DELETE with RETURNING insert into some_table (col_2) values ('new val') returning col_2 into var2; raise log 'logging a value from an INSERT (%)', var2; ```

Database functions vs Edge Functions guidance

Use Database Functions for data-intensive operations executed within the database and called via REST/GraphQL API. Use Edge Functions for low-latency use-cases as they are globally-distributed and written in Typescript.

Creating functions in Supabase Dashboard

To create a database function in Supabase: (1) Go to the 'SQL editor' section, (2) Click 'New Query', (3) Enter the SQL to create or replace your database function, (4) Click 'Run' or press cmd+enter (ctrl+enter on Windows).

Function return types reference

Functions use `returns type` clause to specify return type. Common examples: `returns text` for strings, `returns bigint` for integers, `returns void` for functions that return nothing, `returns setof table_name` for returning complete table rows, `returns uuid` for UUID values.

Give your agent this brain