How to Write Stored Procedures in Supabase

1-- Simple function that returns a greeting
2create or replace function hello_world()
3returns text
4language sql
5as $$
6  select 'Hello from Supabase!';
7$$;

1create or replace function create_todo(task_name text, user_uuid uuid)
2returns bigint
3language plpgsql
4as $$
5declare
6  new_id bigint;
7begin
8  insert into todos (task, is_complete, user_id)
9  values (task_name, false, user_uuid)
10  returning id into new_id;
11
12  return new_id;
13end;
14$$;

1import { createClient } from '@supabase/supabase-js'
2
3const supabase = createClient(
4  'https://your-project.supabase.co',
5  'your-anon-key'
6)
7
8// Call a simple function
9const { data: greeting } = await supabase.rpc('hello_world')
10console.log(greeting) // 'Hello from Supabase!'
11
12// Call a function with parameters
13const { data: newId, error } = await supabase.rpc('create_todo', {
14  task_name: 'Buy groceries',
15  user_uuid: 'a1b2c3d4-...'
16})
17
18if (error) {
19  console.error('Error:', error.message)
20} else {
21  console.log('Created todo with ID:', newId)
22}

1-- Function that returns a filtered result set
2create or replace function get_user_stats(target_user_id uuid)
3returns table (
4  total_todos bigint,
5  completed_todos bigint,
6  completion_rate numeric
7)
8language plpgsql
9as $$
10begin
11  return query
12  select
13    count(*)::bigint as total_todos,
14    count(*) filter (where is_complete = true)::bigint as completed_todos,
15    case
16      when count(*) > 0
17      then round((count(*) filter (where is_complete = true)::numeric / count(*)::numeric) * 100, 1)
18      else 0
19    end as completion_rate
20  from todos
21  where user_id = target_user_id;
22end;
23$$;
24
25-- Call from JavaScript
26-- const { data } = await supabase.rpc('get_user_stats', { target_user_id: userId })

1-- SECURITY INVOKER (default, recommended)
2-- RLS policies apply, runs as the calling user
3create or replace function get_my_profile()
4returns json
5language plpgsql
6security invoker
7as $$
8begin
9  return (
10    select row_to_json(p)
11    from profiles p
12    where p.id = auth.uid()
13  );
14end;
15$$;
16
17-- SECURITY DEFINER (elevated privileges)
18-- Bypasses RLS, runs as function owner
19-- MUST set search_path = '' for security
20create or replace function admin_create_user_profile(
21  target_user_id uuid,
22  display_name text
23)
24returns void
25language plpgsql
26security definer
27set search_path = ''
28as $$
29begin
30  insert into public.profiles (id, name)
31  values (target_user_id, display_name);
32end;
33$$;
34
35-- Restrict who can call the definer function
36revoke execute on function admin_create_user_profile from public, anon;
37grant execute on function admin_create_user_profile to authenticated;