# Database functions

Creating and using Postgres functions.

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](https://docs-2i73v86dq-supabase.vercel.app/docs/guides/database/debugging-functions) covers logging and error handling.

## 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](../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

```

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.

Caution: Overloaded functions aren't supported. Give every function a unique name.

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

**SQL**

```sql
select hello_world();
```

**JavaScript**

```js
const { data, error } = await supabase.rpc('hello_world')
```

Reference: [`rpc()`](../../reference/javascript/rpc)

**Dart**

```dart
final data = await supabase
  .rpc('hello_world');
```

Reference: [`rpc()`](../../reference/dart/rpc)

**Swift**

```swift
try await supabase.rpc("hello_world").execute()
```

Reference: [`rpc()`](../../reference/swift/rpc)

**Kotlin**

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

Reference: [`rpc()`](../../reference/kotlin/rpc)

**Python**

```python
data = supabase.rpc('hello_world').execute()
```

Reference: [`rpc()`](../../reference/python/rpc)

**C#**

```c#
await supabase.Rpc("hello_world", null);
```

Reference: [`Rpc()`](../../reference/csharp/rpc)

### 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.

**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         |
```

**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);
```

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:

**SQL**

```sql
select *
from get_planets()
where id = 1;
```

**JavaScript**

```js
const { data, error } = supabase.rpc('get_planets').eq('id', 1)
```

**Dart**

```dart
final data = await supabase
  .rpc('get_planets')
  .eq('id', 1);
```

**Swift**

```swift
let response = try await supabase.rpc("get_planets").eq("id", value: 1).execute()
```

**Kotlin**

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

**Python**

```python
data = supabase.rpc('get_planets').eq('id', 1).execute()
```

### 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:

**SQL**

```sql
select * from add_planet('Jakku');
```

**JavaScript**

```js
const { data, error } = await supabase.rpc('add_planet', { name: 'Jakku' })
```

**Dart**

```dart
final data = await supabase
  .rpc('add_planet', params: { 'name': 'Jakku' });
```

**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()
```

**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")
    }
)
```

**Python**

```python
data = supabase.rpc('add_planet', params={'name': 'Jakku'}).execute()
```

**C#**

```c#
await supabase.Rpc("add_planet", new Dictionary<string, object> { { "name", "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:

```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.

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.

### 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;
   ```

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:

   ```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:

   ```sql
   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:

```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



### Call a database function from JavaScript



### Call an external API from a database function