How to create and invoke Supabase database functions

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

Supabase database functions are PostgreSQL functions that you can invoke from your Next.js 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.

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.

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.

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.

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.

SELECT * FROM get_restaurants();

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

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

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

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.

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.

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.

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.

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