# How to Write Stored Procedures in Supabase

- Tool: Supabase
- Difficulty: Beginner
- Time required: 10-15 min
- Compatibility: Supabase (all plans), @supabase/supabase-js v2+, PostgreSQL 15+
- Last updated: March 2026

## TL;DR

Stored procedures in Supabase are PostgreSQL functions written in PL/pgSQL or SQL. You create them in the SQL Editor using CREATE OR REPLACE FUNCTION, then call them from your frontend using supabase.rpc(). They are ideal for complex queries, atomic multi-table operations, and business logic that should run inside the database rather than on the client. Choose security invoker for user-context execution or security definer for elevated privileges.

## Writing and Calling Stored Procedures in Supabase

Supabase exposes PostgreSQL functions through its auto-generated REST API, so any function you create in SQL becomes callable from your frontend via supabase.rpc(). This tutorial covers creating functions in PL/pgSQL and SQL, passing parameters, returning data, understanding security modes, and deciding when a database function is the right choice over an Edge Function.

## Before you start

- A Supabase project with at least one table
- Access to the SQL Editor in the Supabase Dashboard
- Basic understanding of SQL and PostgreSQL
- @supabase/supabase-js installed in your frontend project

## Step-by-step guide

### 1. Create a simple SQL function in the SQL Editor

Open the SQL Editor in your Supabase Dashboard (left sidebar > SQL Editor). Write a simple function that returns a value. SQL-language functions are the simplest type — they contain a single SQL statement. Click Run to create the function. Once created, it is automatically available through the REST API and can be called with supabase.rpc().

```
-- Simple function that returns a greeting
create or replace function hello_world()
returns text
language sql
as $$
  select 'Hello from Supabase!';
$$;
```

**Expected result:** The function is created and visible in Database > Functions in the Dashboard.

### 2. Create a PL/pgSQL function with parameters and logic

PL/pgSQL functions support variables, conditionals, loops, and transaction control. They are the standard choice for business logic that involves multiple steps. This example creates a function that inserts a record and returns the new ID. The DECLARE block defines variables, and the BEGIN/END block contains the logic.

```
create or replace function create_todo(task_name text, user_uuid uuid)
returns bigint
language plpgsql
as $$
declare
  new_id bigint;
begin
  insert into todos (task, is_complete, user_id)
  values (task_name, false, user_uuid)
  returning id into new_id;

  return new_id;
end;
$$;
```

**Expected result:** The function is created and accepts task_name and user_uuid parameters, returning the new record ID.

### 3. Call the function from your frontend with supabase.rpc()

Use the supabase.rpc() method to call any database function from your JavaScript or TypeScript code. Pass the function name as the first argument and parameters as an object. The parameter names must match the function's parameter names exactly. The response follows the same { data, error } pattern as other Supabase queries.

```
import { createClient } from '@supabase/supabase-js'

const supabase = createClient(
  'https://your-project.supabase.co',
  'your-anon-key'
)

// Call a simple function
const { data: greeting } = await supabase.rpc('hello_world')
console.log(greeting) // 'Hello from Supabase!'

// Call a function with parameters
const { data: newId, error } = await supabase.rpc('create_todo', {
  task_name: 'Buy groceries',
  user_uuid: 'a1b2c3d4-...'
})

if (error) {
  console.error('Error:', error.message)
} else {
  console.log('Created todo with ID:', newId)
}
```

**Expected result:** The frontend successfully calls the database function and receives the return value.

### 4. Return a table result set from a function

Functions can return entire result sets using RETURNS TABLE or RETURNS SETOF. This is useful for complex queries that combine multiple tables, apply business logic filters, or calculate aggregates. The result is returned as an array of objects, just like a regular Supabase query.

```
-- Function that returns a filtered result set
create or replace function get_user_stats(target_user_id uuid)
returns table (
  total_todos bigint,
  completed_todos bigint,
  completion_rate numeric
)
language plpgsql
as $$
begin
  return query
  select
    count(*)::bigint as total_todos,
    count(*) filter (where is_complete = true)::bigint as completed_todos,
    case
      when count(*) > 0
      then round((count(*) filter (where is_complete = true)::numeric / count(*)::numeric) * 100, 1)
      else 0
    end as completion_rate
  from todos
  where user_id = target_user_id;
end;
$$;

-- Call from JavaScript
-- const { data } = await supabase.rpc('get_user_stats', { target_user_id: userId })
```

**Expected result:** The function returns a result set with total_todos, completed_todos, and completion_rate columns.

### 5. Understand security invoker vs security definer

By default, functions execute as security invoker — they run with the permissions of the calling user, and RLS policies apply normally. If you need a function to perform operations the user cannot do directly (like inserting into an admin-only table), use security definer. Security definer functions run with the permissions of the function creator (usually the postgres superuser), bypassing RLS. Always set search_path = '' on security definer functions to prevent search path attacks.

```
-- SECURITY INVOKER (default, recommended)
-- RLS policies apply, runs as the calling user
create or replace function get_my_profile()
returns json
language plpgsql
security invoker
as $$
begin
  return (
    select row_to_json(p)
    from profiles p
    where p.id = auth.uid()
  );
end;
$$;

-- SECURITY DEFINER (elevated privileges)
-- Bypasses RLS, runs as function owner
-- MUST set search_path = '' for security
create or replace function admin_create_user_profile(
  target_user_id uuid,
  display_name text
)
returns void
language plpgsql
security definer
set search_path = ''
as $$
begin
  insert into public.profiles (id, name)
  values (target_user_id, display_name);
end;
$$;

-- Restrict who can call the definer function
revoke execute on function admin_create_user_profile from public, anon;
grant execute on function admin_create_user_profile to authenticated;
```

**Expected result:** Security invoker functions respect RLS; security definer functions bypass it with proper safeguards.

## Complete code example

File: `migration.sql`

```sql
-- Complete example: Stored procedures for a todo app
-- Run this in the Supabase SQL Editor

-- 1. Simple query function (security invoker = respects RLS)
create or replace function get_my_todos()
returns setof todos
language sql
security invoker
as $$
  select * from todos
  where user_id = auth.uid()
  order by created_at desc;
$$;

-- 2. Insert function with validation
create or replace function create_todo(task_name text)
returns bigint
language plpgsql
security invoker
as $$
declare
  new_id bigint;
begin
  if trim(task_name) = '' then
    raise exception 'Task name cannot be empty';
  end if;

  insert into todos (task, is_complete, user_id)
  values (trim(task_name), false, auth.uid())
  returning id into new_id;

  return new_id;
end;
$$;

-- 3. Aggregate function returning stats
create or replace function get_my_stats()
returns table (
  total bigint,
  completed bigint,
  pending bigint
)
language plpgsql
security invoker
as $$
begin
  return query
  select
    count(*)::bigint,
    count(*) filter (where is_complete)::bigint,
    count(*) filter (where not is_complete)::bigint
  from todos
  where user_id = auth.uid();
end;
$$;

-- 4. Batch operation (security definer for admin)
create or replace function admin_mark_all_complete(target_user_id uuid)
returns integer
language plpgsql
security definer
set search_path = ''
as $$
declare
  affected integer;
begin
  update public.todos
  set is_complete = true
  where user_id = target_user_id
    and is_complete = false;

  get diagnostics affected = row_count;
  return affected;
end;
$$;

revoke execute on function admin_mark_all_complete from public, anon;
grant execute on function admin_mark_all_complete to authenticated;
```

## Common mistakes

- **Using security definer without setting search_path = '', creating a potential SQL injection vector** — undefined Fix: Always add 'set search_path = ''' to security definer functions and use fully qualified table names (public.tablename) inside the function body.
- **Expecting RLS to be bypassed when using security invoker (the default)** — undefined Fix: Security invoker functions run with the calling user's permissions. If RLS blocks the user, the function fails too. Use security definer only when you intentionally need to bypass RLS.
- **Passing JavaScript camelCase parameter names that do not match the SQL snake_case function parameters** — undefined Fix: Parameter names in supabase.rpc() must match the SQL function parameter names exactly. Use snake_case in both places: supabase.rpc('create_todo', { task_name: 'Buy milk' }).
- **Creating functions in a non-public schema and expecting them to be available via the REST API** — undefined Fix: Only functions in the public schema are exposed through the auto-generated REST API. If you need a function in another schema, call it via an Edge Function instead.

## Best practices

- Use CREATE OR REPLACE so you can update functions without dropping them first
- Default to security invoker — only use security definer when the function genuinely needs to bypass RLS
- Always set search_path = '' on security definer functions and use fully qualified table names
- Use REVOKE and GRANT to control which Postgres roles can execute sensitive functions
- Validate inputs inside PL/pgSQL functions using RAISE EXCEPTION for clear error messages
- Use RETURNS TABLE for complex queries and RETURNS SETOF for returning existing table rows
- Prefer database functions over Edge Functions when the logic is purely data manipulation — they are faster and run inside the database
- Use Edge Functions instead when you need to call external APIs, process files, or run long-running tasks

## Frequently asked questions

### What is the difference between a stored procedure and a function in Supabase?

In PostgreSQL (and Supabase), CREATE FUNCTION is the standard way to create reusable database logic. PostgreSQL also supports CREATE PROCEDURE (since version 11) for transaction control, but Supabase's REST API only exposes functions. Use CREATE FUNCTION for everything you want to call from the frontend.

### When should I use a database function instead of an Edge Function?

Use database functions for operations that are purely data-related: complex queries, multi-table inserts, aggregations, and validations. Use Edge Functions when you need to call external APIs, process files, send emails, or run logic that takes longer than a database transaction should.

### Can I call a stored procedure that performs INSERT operations without RLS policies?

If the function uses security invoker (default), RLS policies must allow the operation. If RLS is enabled with no INSERT policy, the insert will silently fail. Either create an INSERT policy or use security definer to bypass RLS.

### How do I debug a stored procedure that returns unexpected results?

Use RAISE NOTICE inside PL/pgSQL functions to log debug messages. Check the Postgres logs in the Dashboard under Logs > Postgres. You can also test functions directly in the SQL Editor with SELECT statements.

### Can I use JavaScript or TypeScript for stored procedures?

Not directly in the database. Supabase database functions use SQL or PL/pgSQL. If you need to run TypeScript logic on the server, use an Edge Function instead. You can combine both — an Edge Function that calls a database function via supabase.rpc().

### Are stored procedures faster than client-side queries?

Yes, for multi-step operations. A stored procedure runs entirely inside the database in a single round trip, while multiple client-side queries require separate network requests. For single queries, the performance difference is negligible.

### Can RapidDev help write stored procedures for my Supabase project?

Yes. RapidDev can design and implement database functions, triggers, and RLS policies for complex business logic, ensuring proper security and performance in your Supabase backend.

---

Source: https://www.rapidevelopers.com/supabase-tutorial/how-to-write-stored-procedures-in-supabase
© RapidDev — https://www.rapidevelopers.com/supabase-tutorial/how-to-write-stored-procedures-in-supabase