Database Functions
Postgres has built-in support for SQL functions. These functions live inside your database, and they can be used with the API.
Quick demo
Getting started
Supabase provides several options for creating database functions. You can use the Dashboard or create them directly using SQL. We provide a SQL editor within the Dashboard, or you can connect to your database and run the SQL queries yourself.
- Go to the "SQL editor" section.
- Click "New Query".
- Enter the SQL to create or replace your Database function.
- Click "Run" or cmd+enter (ctrl+enter).
Simple functions
Let's create a basic Database Function which returns a string "hello world".
_10create or replace function hello_world() -- 1_10returns text -- 2_10language sql -- 3_10as $$ -- 4_10 select 'hello world'; -- 5_10$$; --6
Show/Hide Details
At it's most basic a function has the following parts:
create or replace function hello_world()
: The function declaration, wherehello_world
is the name of the function. You can use eithercreate
when creating a new function orreplace
when replacing an existing function. Or you can usecreate or replace
together to handle either.returns text
: The type of data that the function returns. If it returns nothing, you canreturns void
.language sql
: The language used inside the function body. This can also be a procedural language:plpgsql
,plv8
,plpython
, etc.as $$
: The function wrapper. Anything enclosed inside the$$
symbols will be part of the function body.select 'hello world';
: A simple function body. The finalselect
statement inside a function body will be returned if there are no statements following it.$$;
: The closing symbols of the function wrapper.
After the Function is created, we have several ways of "executing" the function - either directly inside the database using SQL, or with one of the client libraries.
_10select hello_world();
Returning data sets
Database Functions can also return data sets from Tables or Views.
For example, if we had a database with some Star Wars data inside:
Planets
id | name |
---|---|
1 | Tattoine |
2 | Alderaan |
3 | Kashyyyk |
People
id | name | planet_id |
---|---|---|
1 | Anakin Skywalker | 1 |
2 | Luke Skywalker | 1 |
3 | Princess Leia | 2 |
4 | Chewbacca | 3 |
We could create a function which returns all the planets:
_10create or replace function get_planets()_10returns setof planets_10language sql_10as $$_10 select * from planets;_10$$;
Because this function returns a table set, we can also apply filters and selectors. For example, if we only wanted the first planet:
_10select *_10from get_planets()_10where id = 1;
Passing parameters
Let's create a Function to insert a new planet into the planets
table and return the new ID. Note that this time we're using the plpgsql
language.
_14create or replace function add_planet(name text)_14returns bigint_14language plpgsql_14as $$_14declare_14 new_row bigint;_14begin_14 insert into planets(name)_14 values (add_planet.name)_14 returning id into new_row;_14_14 return new_row;_14end;_14$$;
Once again, you can execute this function either inside your database using a select
query, or with the client libraries:
_10select * from add_planet('Jakku');
Suggestions
Database Functions vs Edge Functions
For data-intensive operations, use Database Functions, which are executed within your database and can be called remotely using the REST and GraphQL API.
For use-cases which require low-latency, use Edge Functions, which are globally-distributed and can be written in Typescript.
Security definer
vs invoker
Postgres allows you to specify whether you want the function to be executed as the user calling the function (invoker
), or as the creator of the function (definer
). For example:
_10create function hello_world()_10returns text_10language plpgsql_10security definer set search_path = ''_10as $$_10begin_10 select 'hello world';_10end;_10$$;
It is best practice to use security invoker
(which is also the default). If you ever use security definer
, you must set the search_path
.
This limits the potential damage if you allow access to schemas which the user executing the function should not have.
Function privileges
By default, database functions can be executed by any role. There are two main ways to restrict this:
-
On a case-by-case basis. Specifically revoke permissions for functions you want to protect. Execution needs to be revoked for both
public
and the role you're restricting:_10revoke execute on function public.hello_world from public;_10revoke execute on function public.hello_world from anon; -
Restrict function execution by default. Specifically grant access when you want a function to be executable by a specific role.
To restrict all existing functions, revoke execution permissions from both
public
and the role you want to restrict:_10revoke execute on all functions in schema public from public;_10revoke execute on all functions in schema public from anon, authenticated;To restrict all new functions, change the default privileges for both
public
and the role you want to restrict:_10alter default privileges in schema public revoke execute on functions from public;_10alter default privileges in schema public revoke execute on functions from anon, authenticated;You can then regrant permissions for a specific function to a specific role:
_10grant execute on function public.hello_world to authenticated;
Debugging functions
You can add logs to help you debug functions. This is especially recommended for complex functions.
Good targets to log include:
- Values of (non-sensitive) variables
- Returned results from queries
General logging
To create custom logs in the Dashboard's Postgres Logs, you can use the raise
keyword. By default, there are 3 observed severity levels:
log
warning
exception
(error level)
_18create function logging_example(_18 log_message text,_18 warning_message text,_18 error_message text_18)_18returns void_18language plpgsql_18as $$_18begin_18 raise log 'logging message: %', log_message;_18 raise warning 'logging warning: %', warning_message;_18_18 -- immediately ends function and reverts transaction_18 raise exception 'logging error: %', error_message;_18end;_18$$;_18_18select 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:
_15create or replace function error_if_null(some_val text)_15returns text_15language plpgsql_15as $$_15begin_15 -- error if some_val is null_15 if some_val is null then_15 raise exception 'some_val should not be NULL';_15 end if;_15 -- return some_val if it is not null_15 return some_val;_15end;_15$$;_15_15select error_if_null(null);
Value checking is common, so Postgres provides a shorthand: the assert
keyword. It uses the following format:
_10-- throw error when condition is false_10assert <some condition>, 'message';
Below is an example
_22create function assert_example(name text)_22returns uuid_22language plpgsql_22as $$_22declare_22 student_id uuid;_22begin_22 -- save a user's id into the user_id variable_22 select_22 id into student_id_22 from attendance_table_22 where student = name;_22_22 -- throw an error if the student_id is null_22 assert student_id is not null, 'assert_example() ERROR: student not found';_22_22 -- otherwise, return the user's id_22 return student_id;_22end;_22$$;_22_22select assert_example('Harry Potter');
Error messages can also be captured and modified with the exception
keyword:
_13create function error_example()_13returns void_13language plpgsql_13as $$_13begin_13 -- fails: cannot read from nonexistent table_13 select * from table_that_does_not_exist;_13_13 exception_13 when others then_13 raise exception 'An error occurred in function <function name>: %', sqlerrm;_13end;_13$$;
Advanced logging
For more complex functions or complicated debugging, try logging:
- Formatted variables
- Individual rows
- Start and end of function calls
_43create or replace function advanced_example(num int default 10)_43returns text_43language plpgsql_43as $$_43declare_43 var1 int := 20;_43 var2 text;_43begin_43 -- Logging start of function_43 raise log 'logging start of function call: (%)', (select now());_43_43 -- Logging a variable from a SELECT query_43 select_43 col_1 into var1_43 from some_table_43 limit 1;_43 raise log 'logging a variable (%)', var1;_43_43 -- It is also possible to avoid using variables, by returning the values of your query to the log_43 raise log 'logging a query with a single return value(%)', (select col_1 from some_table limit 1);_43_43 -- If necessary, you can even log an entire row as JSON_43 raise log 'logging an entire row as JSON (%)', (select to_jsonb(some_table.*) from some_table limit 1);_43_43 -- When using INSERT or UPDATE, the new value(s) can be returned_43 -- into a variable._43 -- When using DELETE, the deleted value(s) can be returned._43 -- All three operations use "RETURNING value(s) INTO variable(s)" syntax_43 insert into some_table (col_2)_43 values ('new val')_43 returning col_2 into var2;_43_43 raise log 'logging a value from an INSERT (%)', var2;_43_43 return var1 || ',' || var2;_43exception_43 -- Handle exceptions here if needed_43 when others then_43 raise exception 'An error occurred in function <advanced_example>: %', sqlerrm;_43end;_43$$;_43_43select advanced_example();
Resources
- Official Client libraries: JavaScript and Flutter
- Community client libraries: github.com/supabase-community
- PostgreSQL Official Docs: Chapter 9. Functions and Operators
- PostgreSQL Reference: CREATE FUNCTION