The Postgres development platform. Supabase gives you a dedicated Postgres database to build your web, mobile, and AI applications.
TypeScript aialternativeauthdatabasedenoembeddingsexamplefirebasenextjsoauth2pgvectorpostgispostgrespostgresqlpostgrestrealtimesupabasevectorswebsockets
---
id: 'functions'
title: 'Database functions'
description: 'Creating and using Postgres functions.'
video: 'https://www.youtube.com/v/MJZCCpCYEqk'
---
Postgres has built-in support for [SQL functions](https://www.postgresql.org/docs/current/sql-createfunction.html).
These functions live inside your database, and you can call them from your app with [`rpc()`](../../reference/javascript/rpc).
- [Database functions vs Edge Functions](#database-functions-vs-edge-functions) compares the two. Start here if you aren't sure which one fits.
- [Create a database function](#create-a-database-function) has the steps, from a one-line function to one that takes parameters.
- [Secure a database function](#secure-a-database-function) covers which user the function runs as and who can call it.
- [Debugging database functions](/docs/guides/database/debugging-functions) covers logging and error handling.
## Quick demo
<YouTube id="MJZCCpCYEqk" title="Creating and calling Postgres functions" />
## 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](../api).
For use cases that need low latency, use [Edge Functions](../../guides/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](../../guides/database/connecting-to-postgres).
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 [#simple-functions]
Create a basic database function that returns the string "hello world".
```sql
create or replace function hello_world() -- 1
returns text -- 2
language sql -- 3
as $$ -- 4
select 'hello world'; -- 5
$$; --6
```
<details>
<summary>Show/Hide Details</summary>
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.
</details>
<Admonition type="caution">
Overloaded functions aren't supported. Give every function a unique name.
</Admonition>
After you create the function, you can run it inside the database with SQL, or with one of the client libraries.
<Tabs
scrollable
size="small"
type="underlined"
defaultActiveId="sql"
queryGroup="language"
>
<TabPanel id="sql" label="SQL">
```sql
select hello_world();
```
</TabPanel>
<TabPanel id="js" label="JavaScript">
```js
const { data, error } = await supabase.rpc('hello_world')
```
Reference: [`rpc()`](../../reference/javascript/rpc)
</TabPanel>
<$Show if="sdk:dart">
<TabPanel id="dart" label="Dart">
```dart
final data = await supabase
.rpc('hello_world');
```
Reference: [`rpc()`](../../reference/dart/rpc)
</TabPanel>
</$Show>
<$Show if="sdk:swift">
<TabPanel id="swift" label="Swift">
```swift
try await supabase.rpc("hello_world").execute()
```
Reference: [`rpc()`](../../reference/swift/rpc)
</TabPanel>
</$Show>
<$Show if="sdk:kotlin">
<TabPanel id="kotlin" label="Kotlin">
```kotlin
val data = supabase.postgrest.rpc("hello_world")
```
Reference: [`rpc()`](../../reference/kotlin/rpc)
</TabPanel>
</$Show>
<$Show if="sdk:python">
<TabPanel id="python" label="Python">
```python
data = supabase.rpc('hello_world').execute()
```
Reference: [`rpc()`](../../reference/python/rpc)
</TabPanel>
</$Show>
<$Show if="sdk:csharp">
<TabPanel id="csharp" label="C#">
```c#
await supabase.Rpc("hello_world", null);
```
Reference: [`Rpc()`](../../reference/csharp/rpc)
</TabPanel>
</$Show>
</Tabs>
### Returning data sets
A database function can also return a data set from a [table](../../guides/database/tables) or a view.
For example, create two tables holding some Star Wars data. The **Data** tab shows the rows they end up with.
<Tabs
scrollable
size="small"
type="underlined"
defaultActiveId="sql"
queryGroup="example-view"
>
<TabPanel id="data" label="Data">
#### Planets
```
| id | name |
| --- | -------- |
| 1 | Tatooine |
| 2 | Alderaan |
| 3 | Kashyyyk |
```
#### People
```
| id | name | planet_id |
| --- | ---------------- | --------- |
| 1 | Anakin Skywalker | 1 |
| 2 | Luke Skywalker | 1 |
| 3 | Princess Leia | 2 |
| 4 | Chewbacca | 3 |
```
</TabPanel>
<TabPanel id="sql" label="SQL">
```sql
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);
```
</TabPanel>
</Tabs>
The following function returns all the planets:
```sql
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:
<Tabs
scrollable
size="small"
type="underlined"
defaultActiveId="sql"
queryGroup="language"
>
<TabPanel id="sql" label="SQL">
```sql
select *
from get_planets()
where id = 1;
```
</TabPanel>
<TabPanel id="js" label="JavaScript">
```js
const { data, error } = supabase.rpc('get_planets').eq('id', 1)
```
</TabPanel>
<$Show if="sdk:dart">
<TabPanel id="dart" label="Dart">
```dart
final data = await supabase
.rpc('get_planets')
.eq('id', 1);
```
</TabPanel>
</$Show>
<$Show if="sdk:swift">
<TabPanel id="swift" label="Swift">
```swift
let response = try await supabase.rpc("get_planets").eq("id", value: 1).execute()
```
</TabPanel>
</$Show>
<$Show if="sdk:kotlin">
<TabPanel id="kotlin" label="Kotlin">
```kotlin
val data = supabase.postgrest.rpc("get_planets") {
filter {
eq("id", 1)
}
}
```
</TabPanel>
</$Show>
<$Show if="sdk:python">
<TabPanel id="python" label="Python">
```python
data = supabase.rpc('get_planets').eq('id', 1).execute()
```
</TabPanel>
</$Show>
</Tabs>
### 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.
```sql
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:
<Tabs
scrollable
size="small"
type="underlined"
defaultActiveId="sql"
queryGroup="language"
>
<TabPanel id="sql" label="SQL">
```sql
select * from add_planet('Jakku');
```
</TabPanel>
<TabPanel id="js" label="JavaScript">
```js
const { data, error } = await supabase.rpc('add_planet', { name: 'Jakku' })
```
</TabPanel>
<$Show if="sdk:dart">
<TabPanel id="dart" label="Dart">
```dart
final data = await supabase
.rpc('add_planet', params: { 'name': 'Jakku' });
```
</TabPanel>
</$Show>
<$Show if="sdk:swift">
<TabPanel id="swift" label="Swift">
Using `Encodable` type:
```swift
struct Planet: Encodable {
let name: String
}
try await supabase.rpc(
"add_planet",
params: Planet(name: "Jakku")
)
.execute()
```
Using `AnyJSON` convenience` type:
```swift
try await supabase.rpc(
"add_planet",
params: ["name": AnyJSON.string("Jakku")]
)
.execute()
```
</TabPanel>
</$Show>
<$Show if="sdk:kotlin">
<TabPanel id="kotlin" label="Kotlin">
```kotlin
val data = supabase.postgrest.rpc(
function = "add_planet",
parameters = buildJsonObject { //You can put here any serializable object including your own classes
put("name", "Jakku")
}
)
```
</TabPanel>
</$Show>
<$Show if="sdk:python">
<TabPanel id="python" label="Python">
```python
data = supabase.rpc('add_planet', params={'name': 'Jakku'}).execute()
```
</TabPanel>
</$Show>
<$Show if="sdk:csharp">
<TabPanel id="csharp" label="C#">
```c#
await supabase.Rpc("add_planet", new Dictionary<string, object> { { "name", "Jakku" } });
```
</TabPanel>
</$Show>
</Tabs>
## 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:
```sql
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.
<Admonition type="danger">
A `security definer` function can return rows the caller isn't allowed to read. It runs with its creator's privileges. A function created in the Dashboard or by a migration is owned by `postgres`, which bypasses Row Level Security. Any role can call it by default, including `anon`, the role that serves requests carrying no user session.
Pinning the `search_path` doesn't change who can call the function. When a `security definer` function reads per-user data:
- **Check ownership in the function body.** An execute grant controls which roles can call the function. It doesn't limit the rows a permitted role gets back, so granting to `authenticated` still returns any user's row to every signed-in caller.
- **Narrow the execute privilege as well.** Revoke it from `public` and from each role holding a direct grant, such as `anon`. Then grant it to the roles that need it. See the [Function privileges](#function-privileges) section of this page.
</Admonition>
### 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:
```sql
revoke execute on function public.hello_world from public;
revoke execute on function public.hello_world from anon;
```
1. 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:
```sql
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. Postgres grants `execute` to `PUBLIC` through a global default, so revoke it without `in schema`. A revoke limited to one schema has no effect on a global default:
```sql
alter default privileges revoke execute on functions from public;
alter default privileges in schema public revoke execute on functions from anon, authenticated;
```
The first statement applies to every function your role creates later, in every schema. Grant `execute` on each new function to the roles that call it, including helper functions that your Row Level Security policies call.
You can then regrant permissions for a specific function to a specific role:
```sql
grant execute on function public.hello_world to authenticated;
```
## Resources
- Official Client libraries: [JavaScript](../../reference/javascript/rpc) and [Flutter](../../reference/dart/rpc)
- Community client libraries: [github.com/supabase-community](https://github.com/supabase-community)
- Postgres Official Docs: [Chapter 9. Functions and Operators](https://www.postgresql.org/docs/current/functions.html)
- Postgres Reference: [CREATE FUNCTION](https://www.postgresql.org/docs/current/sql-createfunction.html)
### Create a database function in the Dashboard
<YouTube id="MJZCCpCYEqk" title="Creating a Postgres function in the dashboard" />
### Call a database function from JavaScript
<YouTube id="I6nnp9AINJk" title="Calling a Postgres function from JavaScript" />
### Call an external API from a database function
<YouTube id="rARgrELRCwY" title="Calling an external API from a Postgres function" />