Database Functions & Triggers
PostgreSQL functions and triggers let you run server-side logic directly in your database. This is powerful for enforcing business rules, automating tasks, and keeping data consistent without relying on application code. Supabase gives you full access to PL/pgSQL, PostgreSQL's procedural language.
🚀 Use Cases
- Auto-timestamps: Automatically set updated_at on row changes
- Audit logs: Record every insert, update, and delete
- Slug generation: Create URL slugs from titles automatically
- Cascade operations: Update related records when data changes
- Computed columns: Derive values from other columns
Creating Database Functions
Basic Function
-- A simple function that returns a greeting
CREATE OR REPLACE FUNCTION hello(name text)
RETURNS text
LANGUAGE plpgsql
AS $$
BEGIN
RETURN 'Hello, ' || name || '!';
END;
$$;
-- Call it from Supabase client
-- const { data } = await supabase.rpc('hello', { name: 'World' })Auto-Update Timestamps
-- Function to auto-update the updated_at column
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$;
-- Create the trigger on your table
CREATE TRIGGER set_updated_at
BEFORE UPDATE ON posts
FOR EACH ROW
EXECUTE FUNCTION update_updated_at();
-- Now every UPDATE on 'posts' automatically sets updated_atAudit Log Trigger
Track every change to critical tables with an automatic audit log.
-- Create an audit log table
CREATE TABLE audit_log (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
table_name text NOT NULL,
operation text NOT NULL,
old_data jsonb,
new_data jsonb,
changed_by uuid DEFAULT auth.uid(),
changed_at timestamptz DEFAULT NOW()
);
-- Generic audit trigger function
CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
INSERT INTO audit_log (table_name, operation, old_data, new_data)
VALUES (
TG_TABLE_NAME,
TG_OP,
CASE WHEN TG_OP IN ('UPDATE', 'DELETE') THEN to_jsonb(OLD) END,
CASE WHEN TG_OP IN ('INSERT', 'UPDATE') THEN to_jsonb(NEW) END
);
RETURN COALESCE(NEW, OLD);
END;
$$;
-- Attach to any table
CREATE TRIGGER audit_posts
AFTER INSERT OR UPDATE OR DELETE ON posts
FOR EACH ROW EXECUTE FUNCTION audit_trigger();Auto-Generate Slugs
-- Function to generate a URL-safe slug from a title
CREATE OR REPLACE FUNCTION generate_slug()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
NEW.slug = LOWER(
REGEXP_REPLACE(
REGEXP_REPLACE(NEW.title, '[^a-zA-Z0-9\s-]', '', 'g'),
'\s+', '-', 'g'
)
);
RETURN NEW;
END;
$$;
CREATE TRIGGER set_slug
BEFORE INSERT ON articles
FOR EACH ROW
WHEN (NEW.slug IS NULL)
EXECUTE FUNCTION generate_slug();Calling Functions from the Client
// Call an RPC function from your app
const { data, error } = await supabase.rpc('get_user_stats', {
user_id: 'some-uuid'
})
// Function that returns a set of rows
const { data: leaderboard } = await supabase
.rpc('get_leaderboard', { limit_count: 10 })⚠️ SECURITY DEFINER vs SECURITY INVOKER
SECURITY DEFINER runs with the privileges of the function creator (bypasses RLS).
SECURITY INVOKER (default) runs with the caller's privileges. Use DEFINER carefully
and always set search_path = '' to prevent search path attacks.
💡 Key Takeaways
- • Use triggers for automatic side effects like timestamps and audit logs
- • PL/pgSQL functions run inside the database for maximum performance
- • Call functions from the client with
supabase.rpc() - • Be careful with SECURITY DEFINER — it bypasses RLS
- • Triggers fire automatically on INSERT, UPDATE, or DELETE operations
📚 Learn More
-
Database Functions →
Official guide to PostgreSQL functions in Supabase.
-
Database Webhooks →
Trigger HTTP requests from database events.