Skip to content
Database

Database functions

Postgres has built-in support for SQL functions. These functions live inside your database, and you can call them from your app with rpc().

Quick demo#

Database functions vs Edge Functions#

For data-intensive operations, use database functions. They run inside your database, and you can call them remotely with the REST and GraphQL API.

For use cases that need low latency, use Edge Functions. They're globally distributed and you write them in TypeScript.

Create a database function#

Getting started#

Create a database function from the Dashboard, or write the SQL yourself against a direct connection.

To use the Dashboard:

  1. Go to the SQL Editor section.
  2. Click New Query.
  3. Enter the SQL that creates or replaces your database function.
  4. Click Run. You can also press cmd+enter or ctrl+enter.

Basic functions #

Create a basic database function that returns the 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 Details

At its 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. Use create for a new function, replace for one that exists, or create or replace when the function might not exist yet.
  2. returns text: The type of data the function returns. For a function that returns nothing, write returns void.
  3. language sql: The language used inside the function body. This can also be a procedural language: plpgsql, plpython, etc.
  4. as $$: The function wrapper. Anything inside the $$ symbols is part of the function body.
  5. select 'hello world';: A basic function body. The function returns the result of its final query, which can be an insert, update, or delete with a returning clause.
  6. $$;: The closing symbols of the function wrapper.

After you create the function, you can run it inside the database with SQL, or with one of the client libraries.

select hello_world();

Returning data sets#

A database function can also return a data set from a table or a view.

For example, create two tables holding some Star Wars data. The Data tab shows the rows they end up with.

create table planets (
  id serial primary key,
  name text
);
insert into planets
  (name)
values
  ('Tatooine'),
  ('Alderaan'),
  ('Kashyyyk');
create table people (
  id serial primary key,
  name text,
  planet_id bigint references planets
);
insert into people
  (name, planet_id)
values
  ('Anakin Skywalker', 1),
  ('Luke Skywalker', 1),
  ('Princess Leia', 2),
  ('Chewbacca', 3);

The following function returns all the planets:

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

Because this function returns a table set, you can apply filters and selectors to it. To get the first planet only:

select *
from get_planets()
where id = 1;

Passing parameters#

Create a function that inserts a new planet into the planets table and returns the new ID. This function uses 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;
$$;

You can run this function inside your database with a select query, or with the client libraries:

select * from add_planet('Jakku');

Secure a database function#

Security definer vs invoker#

Postgres runs a function either as the user calling it (invoker) or as its creator (definer). For example:

create or replace function hello_world()
returns text
language plpgsql
security definer set search_path = ''
as $$
begin
  return 'hello world';
end;
$$;

Prefer security invoker, which is also the default. When you use security definer, you must set the search_path.

With an empty search path, search_path = '', name the schema for every relation in the function body, such as from public.table. An empty search path limits the damage when the function can reach a schema you don't want the calling user to reach.

Function privileges#

By default, any role can run a database function. You can restrict execution in two ways:

  1. Revoke on a case-by-case basis. Revoke execute for the functions you want to protect, from both public and the role you're restricting:

    revoke execute on function public.hello_world from public;
    revoke execute on function public.hello_world from anon;

  2. Restrict execution by default, then grant access to the roles that need each function.

    To restrict every function that exists, revoke execute from both public and the role you want to restrict:

    revoke execute on all functions in schema public from public;
    revoke execute on all functions in schema public from anon, authenticated;

    To restrict every function created later, change the default privileges for both public and the role you want to restrict:

    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;

You can then regrant permissions for a specific function to a specific role:

grant execute on function public.hello_world to authenticated;

Resources#

Create a database function in the Dashboard#

Call a database function from JavaScript#

Call an external API from a database function#