Supabase ⚡ database functions
Tags: javascript, supabase, webdev, opensource
Published: 2023-08-31 04:21:01

---
title: Supabase ⚡ database functions
description: Learn about Supabase database functions and how to invoke them from your JavaScript app.
slug: create-invoke-supabase-database-functions
published: true
canonical_url: https://www.kirandev.com/create-invoke-supabase-database-functions
tags: javascript, supabase, webdev, opensource
---

In this article, we will learn about Supabase database functions and how to invoke them from your app.

Supabase database functions are [PostgreSQL functions](https://www.postgresql.org/docs/current/functions.html) that you can invoke from your app using the Supabase client.

Database function allow you to carry out operations that would normally take several queries and round trips in a single function within the database.

## Create a table

Let's create a new table called `restaurants` and add some data to it.

```sql
CREATE TABLE restaurants (
  id INT PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT NOT NULL,
  address TEXT NOT NULL,
  phone_number TEXT NOT NULL,
  email TEXT NOT NULL,
  website TEXT NOT NULL,
  status TEXT NOT NULL
);
```

Add some restaurants to the table so we can test our database function. We'll add 5 restaurants to the table.

```sql
INSERT INTO restaurants (id, name, description, address, phone_number, email, website, status)
VALUES (1, 'Tasty Bites', 'A cozy bistro with a wide range of delicious dishes.', '123 Main St, Cityville', '+1234567890', 'info@tastybites.com', 'www.tastybites.com', 'approved');

INSERT INTO restaurants (id, name, description, address, phone_number, email, website, status)
VALUES (2, 'Sushi Delight', 'Experience the finest sushi in town.', '456 Oak Ave, Urbantown', '+9876543210', 'hello@sushidelight.com', 'www.sushidelight.com', 'approved');

INSERT INTO restaurants (id, name, description, address, phone_number, email, website, status)
VALUES (3, 'Pizza Haven', 'Authentic Italian pizzas made with love.', '789 Pine Rd, Villageland', '+5551234567', 'info@pizzahaven.net', 'www.pizzahaven.net', 'pending');

INSERT INTO restaurants (id, name, description, address, phone_number, email, website, status)
VALUES (4, 'Spice Fusion', 'A fusion of flavors from around the world.', '1010 Spice Blvd, Flavortown', '+1231231234', 'contact@spicefusion.co', 'www.spicefusion.co', 'approved');

INSERT INTO restaurants (id, name, description, address, phone_number, email, website, status)
VALUES (5, 'Café Euphoria', 'A serene café serving premium coffees and pastries.', '222 Serenity Ln, Tranquiltown', '+9879879876', 'info@cafeeuphoria.com', 'www.cafeeuphoria.com', 'rejected');
```

## Explore the database function syntax

Here is the basic syntax for creating a database function.

```sql
CREATE FUNCTION function_name (arguments) RETURNS return_type AS $$
  DECLARE
    -- declare variables
  BEGIN
    -- function body
  END;
$$ LANGUAGE plpgsql;
```

A database function has the following parts:

- `function_name` is the name of the function.
- `arguments` are the arguments that the function accepts.
- `return_type` is the type of value that the function returns.
- `function body` is the code that the function executes.

## Function that returns records

Let's create a simple function that returns the all the records from the `restaurants` table.

```sql
CREATE OR REPLACE FUNCTION get_restaurants()
  RETURNS SETOF restaurants AS $$
  BEGIN
    RETURN QUERY SELECT * FROM restaurants;
  END;
$$ LANGUAGE plpgsql;
```

You can see the `get_restaurants` function returns `SETOF restaurants`. This means that the function returns a set of records from the `restaurants` table.

You can invoke the function from the SQL editor or from your Next.js app.

```sql
SELECT * FROM get_restaurants();
```

Call the function from your Next.js app using the Supabase client.

```js
const { data, error } = await supabase.rpc("get_restaurants");
```

You can also write the same function using the `RETURNS TABLE` syntax.

```sql
CREATE OR REPLACE FUNCTION get_restaurants_2()
  RETURNS TABLE (
    id INT,
    name TEXT,
    description TEXT,
    address TEXT,
    phone_number TEXT,
    email TEXT,
    website TEXT,
    status TEXT
  ) AS $$
  BEGIN
    RETURN QUERY SELECT * FROM restaurants;
  END;
$$ LANGUAGE plpgsql;
```

## Function that returns specific columns

If you want to ignore certain columns from the `restaurants` table, you can do so by not including them in the `RETURNS TABLE` syntax and select only the columns you want to return from the `restaurants` table.

In this example, we only returns the `id`, `name`, `email`, and `website` columns from the `restaurants` table.

```sql
CREATE OR REPLACE FUNCTION get_restaurants_3()
  RETURNS TABLE (
    id INT,
    name TEXT,
    email TEXT,
    website TEXT
  ) AS $$
  BEGIN
    RETURN QUERY SELECT r.id, r.name, r.email, r.website FROM restaurants r;
  END;
$$ LANGUAGE plpgsql;
```

## Function that returns a single value

You can also create a function that returns a single value. In this example, we return the total number of restaurants in the `restaurants` table.

```sql
CREATE OR REPLACE FUNCTION get_restaurants_count()
  RETURNS INT AS $$
  BEGIN
    RETURN (SELECT COUNT(*) FROM restaurants);
  END;
$$ LANGUAGE plpgsql;
```

## Function that accepts arguments

You can also create a function that accepts arguments. In this example, we create a function that accepts a `status` argument and returns all the restaurants with that status.

```sql
CREATE OR REPLACE FUNCTION get_restaurants_by_status(r_status TEXT)
  RETURNS SETOF restaurants AS $$
  BEGIN
    RETURN QUERY SELECT * FROM restaurants WHERE status = r_status;
  END;
$$ LANGUAGE plpgsql;
```

You can invoke the function using Supabase client as follows.

```js
const { data, error } = await supabase.rpc("get_restaurants_by_status", {
  r_status: "approved",
});
```