Skip to main content

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#

Creating Functions#

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.

  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 cmd+enter (ctrl+enter).

Examples#

Data set#

For this guide we'll use the following example data:

Planets

idname
1Tattoine
2Alderaan
3Kashyyyk

People

idnameplanet_id
1Anakin Skywalker1
2Luke Skywalker1
3Princess Leia2
4Chewbacca3

Basic function#

A basic example which returns a string "hello world".

create or replace function hello_world() -- 1
returns text -- 2
language sql -- 3
as $$ -- 4
select 'hello world'; -- 5
$$; --6
Show/Hide DetailsAt it's most basic a function has the following parts:
  1. create or replace function hello_world(): The function declaration, where hello_world is the name of the function. You can use either create when creating a new function or replace when replacing an existing function. Or you can use create or replace together to handle either.
  2. returns text: The type of data that the function returns. If it returns nothing, you can returns void.
  3. language sql: The language used inside the function body. This can also be a procedural language: plpgsql, plv8, plpython, etc.
  4. as $$: The function wrapper. Anything enclosed inside the $$ symbols will be part of the function body.
  5. select 'hello world';: A simple function body. The final select statement inside a function body will be returned if there are no statements following it.
  6. $$;: The closing symbols of the funciton wrapper.

Execute#

select hello_world();

Return table sets#

A basic example which return all the planets in our example data set.

create or replace function get_planets()
returns setof planets
language sql
as $$
select * from planets;
$$;

Execute#

Because this function returns a table set, we can also apply filters and selectors. For example, if we only wanted the first planet:

select *
from get_planets()
where id = 1;

With parameters#

A basic example which will insert a new planet into the planets and return the new ID. Note that this time we're using the plpgsql language.

create or replace function add_planet(name text)
returns bigint
language plpgsql
as $$
declare
new_row bigint;
begin
insert into planets(name)
values (add_planet.name)
returning id into new_row;
return new_row;
end;
$$;

Execute#

Because this function returns a table set, we can also apply filters and selectors. For example, if we only wanted the name of the first planet:

select * from add_planet('Jakku');

Resources#