Function

You can call stored procedures as a "Remote Procedure Call".

On this page

Introduction

Functions are a fancy way of saying that you can put some logic into your database then call it from anywhere.

It’s especially useful when the logic rarely changes – like password resets and updates.

Database function and can be called remotely using the REST and GraphQL API.

Let’s create a basic Database Function which returns a string “hello world”.

reate or replace function hello_world()
returns text
language sql
as $$
  select 'hello world';
$$;

When naming your functions, make the name of the function unique as overloaded functions are not supported.

After the Function is created, we have several ways of “executing” the function.

Directly inside the database using SQL,

select hello_world();

Or with the Kotlin library (rpc):

val data = supabase.postgrest.rpc("hello_world")

When calling rpc with parameters, you have to provide a serializable value in the function parameter.

val rpcParams = City(name = "The Shire")
supabase.postgrest.rpc("echo_city", rpcParams)

Database Functions can also return data sets from Tables or Views.

We could create a function which 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, we can also apply filters and selectors.

For example, if we only wanted the first planet:

val data = supabase.postgrest.rpc("get_planets") {
    filter {
        eq("id", 1)
    }
}