Based in

Nairobi

Loading article…

Based in

Nairobi

Postsupabse, postgres, javascript, sql

supabase functions (not edge)

Supabase An open source alternative to firebase...

Date published

August 28, 2024

More posts

PostAug 16, 2026

Tanstack DB in action

The team at TanStack, makers of TanStack Query (aka react-query), partnered with the ElectricSQL team...

react
tanstackdb
webdev

PostJul 28, 2026

Reasons to Still Use Next.js from a TanStack Start Enjoyer

Why You Might Still Pick Next.js Over TanStack Start From a TanStack Start...

nextjs
tanstack
react

PostJul 25, 2025

🎨 TanStack Start SSR-Friendly Theme Provider

A complete theme management system for React applications with SSR support! ✨ 🌟...

tanstackstart
jamstack
react

Full-stack TypeScript, warm interfaces, strict systems, and occasionally a creature feature.

© 2026 Dennis Waweru

Built from Nairobi with TypeScript

Contents

01Supabase
02Supabase postgres functions
03Useful Supabase PostgreSQL Functions
04Summary

Supabase

An open source alternative to firebase offering

database
realtime

auth

functions

edge-functions

But wait,if they already have functions why do they need edge functions?

why the extra parts

Supabase Functions: Your PostgreSQL Toolbox

Supabase functions, also known as database functions, are essentially PostgreSQL stored procedures. They are executable blocks of SQL code that can be called from within SQL queries.

Edge Functions: Beyond the Database

In contrast, Edge functions are server-side TypeScript functions that run on the Deno runtime. They are similar to Firebase Cloud Functions but offer a more flexible and open-source alternative.

Supabase: A PostgreSQL Platform

Beyond its role as an open-source alternative to Firebase, Supabase has evolved into a comprehensive PostgreSQL platform. It provides first-class support for PostgreSQL functions, integrating them seamlessly into its built-in utilities and allowing you to create and manage custom functions directly from the Supabase dashboard.

Structure of a basic postgres functon

sql

1CREATE FUNCTION my_function() RETURNS int AS $$
2BEGIN
3    RETURN 42;
4END;
5$$ LANGUAGE sql;

Breakdown:

CREATE FUNCTION: This keyword indicates that we're defining a new function.
my_function(): This is the name of the function. You can choose any meaningful name you prefer.
RETURNS int: This specifies the return type of the function. In this case, the function will return an integer value.
AS $$: This begins the function body, enclosed within double dollar signs ($$) to delimit it.
BEGIN: This marks the start of the function's executable code.
RETURN 42;: This statement specifies the value that the function will return. In this case, it's the integer 42.
END;: This marks the end of the function's executable code.
$$ LANGUAGE sql;: This specifies the language in which the function is written. In this case, it's SQL.

Purpose:

This function defines a simple SQL function named my_function that returns the integer value 42. It's a basic example to demonstrate the structure and syntax of a function definition in PostgreSQL.

Key points to remember:

You can replace my_function with any desired function name.
The return type can be any valid data type, such as text, boolean, date, or a user-defined type.
The function body can contain complex logic, including conditional statements, loops, and calls to other functions.
The $$ delimiters are used to enclose the function body in a language-independent manner.
Postgres functions can also be called by postgres TRIGGERS which are like functions but react to specific events like insert, update or delete on a table
to execute this function

sql

1SELECT my_function();

to list this function

sql

1SELECT
2    proname AS function_name,
3    prokind AS function_type
4FROM pg_proc
5WHERE proname = 'my_function';

to drop this function

sql

1DROP FUNCTION my_function();

Supabase postgres functions

Built-in functions

Supabase makes use of postgres functions to perform certain tasks within your database.

short list of exampales includes

sql

1--  list all the supabase functions
2SELECT
3    proname AS function_name,
4    prokind AS function_type
5FROM pg_proc;
6
7--  filter for the session supabase functions function
8SELECT
9    proname AS function_name,
10    prokind AS function_type
11FROM pg_proc
12WHERE proname ILIKE '%session%';
13
14--  selects the curremt jwt
15select auth.jwt()
16
17-- select what role is callig the function (anon or authenticated)
18select auth.role();
19
20-- select the session user
21select session_use;

Supabase functions view on the dashboard To view some of these functions in Supabase, you can check under database > functions

supabase functions view on the dashboard

Useful Supabase PostgreSQL Functions

Creating a user_profile Table on User Signup

Supabase stores user data in the auth.users table, which is private and should not be accessed or modified directly. A recommended approach is to create a public users or user_profiles table and link it to the auth.users table.

While this can be done using client-side SDKs by chaining a create user request with a successful signup request, it's more reliable and efficient to handle it on the Supabase side. This can be achieved using a combination of a TRIGGER and a FUNCTION.

sql

1--   create the user_profiles table
2CREATE TABLE user_profiles (
3  id uuid PRIMARY KEY,
4  FOREIGN KEY (id) REFERENCES auth.users(id),
5  name text,
6  email text
7);
8
9-- create a function that returns a trigger on auth.users
10CREATE 
11OR REPLACE FUNCTION public.create_public_user_profile_table() 
12RETURNS TRIGGER AS $$ 
13BEGIN INSERT INTO public.user_profiles (id,name,email) 
14VALUES 
15  (
16    NEW.id,
17    NEW.raw_user_meta_data ->> 'name',
18    NEW.raw_user_meta_data ->> 'email'
19    -- other fields accessible here 
20--     NEW.raw_user_meta_data ->> 'name',
21-- NEW.raw_user_meta_data ->> 'picture',
22    
23);
24RETURN NEW;
25END;
26$$ LANGUAGE plpgsql SECURITY DEFINER;
27
28-- create the trigger that executes the function on every new user rowcteation(signup)
29CREATE TRIGGER create_public_user_profiles_trigger 
30AFTER INSERT ON auth.users FOR EACH ROW WHEN (
31        NEW.raw_user_meta_data IS NOT NULL
32    )
33EXECUTE FUNCTION public.create_public_user_profile_table ();
34

typescript

1let { data: user_profiles, error } = await supabase
2  .from('user_profiles')
3  .select('*')

Add custom claims on jwt creation (RBAC) supabse has a detailed article and video on this .

we need 2 tabbles

public.roles and public.role_permissions

sql

1-- Custom types
2create type public.app_permission as enum ('channels.delete', 'channels.update', 'messages.update', 'messages.delete');
3create type public.app_role as enum ('admin', 'moderator');
4
5-- USER ROLES
6create table public.user_roles (
7  id        bigint generated by default as identity primary key,
8  user_id   uuid references public.users on delete cascade not null,
9  role      app_role not null,
10  unique (user_id, role)
11);
12comment on table public.user_roles is 'Application roles for each user.';
13
14-- ROLE PERMISSIONS
15create table public.role_permissions (
16  id           bigint generated by default as identity primary key,
17  role         app_role not null,
18  permission   app_permission not null,
19  unique (role, permission)
20);
21comment on table public.role_permissions is 'Application permissions for each role.';

example of user role

| id | user_id | role |
| --- | --- | --- |
| 1 | user-1 | admin |
| 2 | user-2 | moderator |

example of a role permission table

| id | role | permission |
| --- | --- | --- |
| 1 | admin | channels.update |
| 2 | admin | messages.update |
| 3 | admin | messages.delete |
| 4 | admin | messages.delete |
| 5 | moderator | channels.update |
| 6 | moderator | messages.update |

user with user_id = user-1 will have admin and moderator roles and can delete channels and messages

users with user_id = user-2 can only update channels and messages with the moderator role

sql

1-- Create the auth hook function
2create or replace function public.custom_access_token_hook(event jsonb)
3returns jsonb
4language plpgsql
5stable
6as $$
7  declare
8    claims jsonb;
9    user_role public.app_role;
10  begin
11    -- Fetch the user role in the user_roles table
12    select role into user_role from public.user_roles where user_id = (event->>'user_id')::uuid;
13
14    claims := event->'claims';
15
16    if user_role is not null then
17      -- Set the claim
18      claims := jsonb_set(claims, '{user_role}', to_jsonb(user_role));
19    else
20      claims := jsonb_set(claims, '{user_role}', 'null');
21    end if;
22
23    -- Update the 'claims' object in the original event
24    event := jsonb_set(event, '{claims}', claims);
25
26    -- Return the modified or original event
27    return event;
28  end;
29$$;
30
31grant usage on schema public to supabase_auth_admin;
32
33grant execute
34  on function public.custom_access_token_hook
35  to supabase_auth_admin;
36
37revoke execute
38  on function public.custom_access_token_hook
39  from authenticated, anon, public;
40
41grant all
42  on table public.user_roles
43to supabase_auth_admin;
44
45revoke all
46  on table public.user_roles
47  from authenticated, anon, public;
48
49create policy "Allow auth admin to read user roles" ON public.user_roles
50as permissive for select
51to supabase_auth_admin
52using (true)
53

then create a function that will be called to authorize on RLS policies

sql

1create or replace function public.authorize(
2  requested_permission app_permission
3)
4returns boolean as $$
5declare
6  bind_permissions int;
7  user_role public.app_role;
8begin
9  -- Fetch user role once and store it to reduce number of calls
10  select (auth.jwt() ->> 'user_role')::public.app_role into user_role;
11
12  select count(*)
13  into bind_permissions
14  from public.role_permissions
15  where role_permissions.permission = requested_permission
16    and role_permissions.role = user_role;
17
18  return bind_permissions > 0;
19end;
20$$ language plpgsql stable security definer set search_path = '';
21
22--  example RLS policies
23create policy "Allow authorized delete access" on public.channels for delete using ( (SELECT authorize('channels.delete')) );
24create policy "Allow authorized delete access" on public.messages for delete using ( (SELECT authorize('messages.delete')) );
25
26

Improved Text:

Creating RPC Endpoints

Supabase functions can be invoked using the rpc function. This is especially useful for writing custom SQL queries when the built-in PostgreSQL APIs are insufficient, such as calculating vector cosine similarity using pg_vector.

```sql
create or replace function match_documents (
query_embedding vector(384),
match_threshold float,
match_count int
)
returns table (
id bigint,
title text,
body text,
similarity float
)
language sql stable
as $$
select
documents.id,
documents.title,
documents.body,
1 - (documents.embedding <=> query_embedding) as similarity
from documents
where 1 - (documents.embedding <=> query_embedding) > match_threshold
order by (documents.embedding <=> query_embedding) asc
limit match_count;
$$;

typescript

1and call it client side

ts const { data: documents } = await supabaseClient.rpc('match_documents', { query_embedding: embedding, // Pass the embedding you want to compare match_threshold: 0.78, // Choose an appropriate threshold for your data match_count: 10, // Choose the number of matches })

typescript

1**Improved Text:**
2
3**Filtering Out Columns**
4
5To prevent certain columns from being modified on the client, create a simple function that triggers on every insert. This function can omit any extra fields the user might send in the request.
6
7

sql -- check if user with roles authenticated or anon submitted an updatedat column and replace it with the current time , if not (thta is an admin) allow it CREATE or REPLACE function public.omit_updated__at () returns trigger as $$ BEGIN IF auth.role() IS NOT NULL AND auth.role() IN ('anon', 'authenticated') THEN NEW.updated_at = now(); END IF; RETURN NEW; END; $$ language plpgsql;
```

Summary

With a little experimentation, you can unlock the power of Supabase functions and their AI-powered SQL editor. This lowers the barrier to entry for the niche knowledge required to get this working.

Why choose Supabase functions?

Extend Supabase's API: Supabase can only expose so much through its API. Postgres, however, is a powerful database. Any action you can perform with SQL statements can be wrapped in a function and called from the client or by a trigger.
Reduce the need for dedicated backends: Supabase functions can fill the simple gaps left by the client SDKs, allowing you to focus on shipping.
Avoid vendor lock-in: Supabase functions are just Postgres. If you ever need to move to another hosting provider, these functionalities will continue to work.