Supabase Postgres Functions and Triggers: Complete Developer Guide#

Moving logic from your application to the database seems counterintuitive. We've spent years learning to keep business logic in our code, not in SQL.

But Postgres functions and triggers solve real problems: ensuring data consistency across multiple applications, automating repetitive tasks, enforcing complex validation rules, and improving performance by reducing round trips.

This guide shows you when and how to use Postgres functions and triggers in Supabase effectively.

Prerequisites#

• Supabase project
• Basic SQL knowledge
• Understanding of database concepts
• Familiarity with Supabase RLS

When to Use Database Functions#

Use database functions when you need:

Data consistency across applications: Logic that must apply regardless of which app (web, mobile, admin panel) accesses the database.

Atomic operations: Multiple related changes that must succeed or fail together.

Performance: Complex queries that benefit from running close to the data.

Reusable logic: Calculations or validations used in multiple places.

Security: Operations that require elevated privileges without exposing service role key.

Don't use database functions for:

• External API calls (use Edge Functions)
• Complex business logic better suited to application code
• Operations requiring extensive debugging and testing
• Logic that changes frequently

Your First Postgres Function#

Create a function to calculate user statistics:

-- Create function
CREATE OR REPLACE FUNCTION get_user_stats(user_id UUID)
RETURNS JSON
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  result JSON;
BEGIN
  SELECT json_build_object(
    'post_count', (
      SELECT COUNT(*) FROM posts WHERE posts.user_id = get_user_stats.user_id
    ),
    'comment_count', (
      SELECT COUNT(*) FROM comments WHERE comments.user_id = get_user_stats.user_id
    ),
    'total_likes', (
      SELECT COUNT(*) FROM likes WHERE likes.user_id = get_user_stats.user_id
    )
  ) INTO result;
  
  RETURN result;
END;
$$;

Call from Next.js:

const { data, error } = await supabase
  .rpc('get_user_stats', { user_id: userId })

console.log(data)
// { post_count: 42, comment_count: 128, total_likes: 256 }

This executes as a single database call instead of three separate queries.

Function Basics#

Return Types#

Functions can return different types:

-- Return single value
CREATE FUNCTION get_post_count(user_id UUID)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN (SELECT COUNT(*) FROM posts WHERE posts.user_id = get_post_count.user_id);
END;
$$;

-- Return single row
CREATE FUNCTION get_user_profile(user_id UUID)
RETURNS TABLE(name TEXT, email TEXT, created_at TIMESTAMPTZ)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY
  SELECT profiles.name, profiles.email, profiles.created_at
  FROM profiles
  WHERE profiles.id = get_user_profile.user_id;
END;
$$;

-- Return multiple rows
CREATE FUNCTION get_recent_posts(days INTEGER DEFAULT 7)
RETURNS SETOF posts
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY
  SELECT *
  FROM posts
  WHERE created_at > NOW() - (days || ' days')::INTERVAL
  ORDER BY created_at DESC;
END;
$$;

-- Return JSON
CREATE FUNCTION get_dashboard_data()
RETURNS JSON
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN json_build_object(
    'users', (SELECT COUNT(*) FROM profiles),
    'posts', (SELECT COUNT(*) FROM posts),
    'active_today', (SELECT COUNT(DISTINCT user_id) FROM activity WHERE date = CURRENT_DATE)
  );
END;
$$;

Security Modes#

SECURITY DEFINER: Function runs with creator's privileges (bypasses RLS).

CREATE FUNCTION admin_delete_user(user_id UUID)
RETURNS VOID
LANGUAGE plpgsql
SECURITY DEFINER -- Runs as function creator
AS $$
BEGIN
  DELETE FROM profiles WHERE id = user_id;
  DELETE FROM posts WHERE user_id = user_id;
  DELETE FROM comments WHERE user_id = user_id;
END;
$$;

SECURITY INVOKER: Function runs with caller's privileges (respects RLS).

CREATE FUNCTION get_my_posts()
RETURNS SETOF posts
LANGUAGE plpgsql
SECURITY INVOKER -- Runs as caller
AS $$
BEGIN
  RETURN QUERY
  SELECT * FROM posts WHERE user_id = auth.uid();
END;
$$;

Use SECURITY DEFINER carefully. It bypasses RLS and can create security vulnerabilities if not properly validated.

Practical Function Examples#

Soft Delete with Audit Trail#

CREATE FUNCTION soft_delete_post(post_id UUID)
RETURNS BOOLEAN
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  current_user_id UUID;
BEGIN
  -- Get current user
  current_user_id := auth.uid();
  
  -- Verify ownership
  IF NOT EXISTS (
    SELECT 1 FROM posts
    WHERE id = post_id AND user_id = current_user_id
  ) THEN
    RAISE EXCEPTION 'Post not found or access denied';
  END IF;
  
  -- Soft delete
  UPDATE posts
  SET
    deleted_at = NOW(),
    deleted_by = current_user_id
  WHERE id = post_id;
  
  -- Log to audit table
  INSERT INTO audit_logs (
    action,
    table_name,
    record_id,
    user_id,
    created_at
  ) VALUES (
    'soft_delete',
    'posts',
    post_id,
    current_user_id,
    NOW()
  );
  
  RETURN TRUE;
END;
$$;

Increment Counter Safely#

CREATE FUNCTION increment_post_views(post_id UUID)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
DECLARE
  new_count INTEGER;
BEGIN
  UPDATE posts
  SET view_count = view_count + 1
  WHERE id = post_id
  RETURNING view_count INTO new_count;
  
  RETURN new_count;
END;
$$;

Call from Next.js:

const { data: viewCount } = await supabase
  .rpc('increment_post_views', { post_id: postId })

This prevents race conditions that occur with read-then-write patterns.

Complex Search with Ranking#

CREATE FUNCTION search_posts(search_query TEXT)
RETURNS TABLE(
  id UUID,
  title TEXT,
  content TEXT,
  rank REAL
)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY
  SELECT
    posts.id,
    posts.title,
    posts.content,
    ts_rank(
      to_tsvector('english', posts.title || ' ' || posts.content),
      plainto_tsquery('english', search_query)
    ) AS rank
  FROM posts
  WHERE
    to_tsvector('english', posts.title || ' ' || posts.content) @@
    plainto_tsquery('english', search_query)
  ORDER BY rank DESC
  LIMIT 20;
END;
$$;

Understanding Triggers#

Triggers automatically execute functions when database events occur.

Trigger Timing#

• BEFORE: Execute before the operation (can modify data or cancel operation)
• AFTER: Execute after the operation (cannot modify data)
• INSTEAD OF: Replace the operation (for views)

Trigger Events#

• INSERT
• UPDATE
• DELETE
• TRUNCATE

Basic Trigger Example#

Automatically set updated_at timestamp:

-- Create trigger function
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$;

-- Attach trigger to table
CREATE TRIGGER set_updated_at
BEFORE UPDATE ON posts
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();

Now every UPDATE automatically sets updated_at:

UPDATE posts SET title = 'New Title' WHERE id = '...';
-- updated_at is automatically set to NOW()

Practical Trigger Examples#

Maintain Denormalized Counts#

Keep comment counts in sync:

-- Function to update comment count
CREATE OR REPLACE FUNCTION update_post_comment_count()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    UPDATE posts
    SET comment_count = comment_count + 1
    WHERE id = NEW.post_id;
    RETURN NEW;
  ELSIF TG_OP = 'DELETE' THEN
    UPDATE posts
    SET comment_count = comment_count - 1
    WHERE id = OLD.post_id;
    RETURN OLD;
  END IF;
END;
$$;

-- Trigger on INSERT
CREATE TRIGGER increment_comment_count
AFTER INSERT ON comments
FOR EACH ROW
EXECUTE FUNCTION update_post_comment_count();

-- Trigger on DELETE
CREATE TRIGGER decrement_comment_count
AFTER DELETE ON comments
FOR EACH ROW
EXECUTE FUNCTION update_post_comment_count();

Validate Data Before Insert#

Prevent invalid data:

CREATE OR REPLACE FUNCTION validate_post()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  -- Title must be at least 3 characters
  IF LENGTH(NEW.title) < 3 THEN
    RAISE EXCEPTION 'Title must be at least 3 characters';
  END IF;
  
  -- Content must be at least 10 characters
  IF LENGTH(NEW.content) < 10 THEN
    RAISE EXCEPTION 'Content must be at least 10 characters';
  END IF;
  
  -- Slug must be unique
  IF EXISTS (
    SELECT 1 FROM posts
    WHERE slug = NEW.slug AND id != COALESCE(NEW.id, '00000000-0000-0000-0000-000000000000'::UUID)
  ) THEN
    RAISE EXCEPTION 'Slug already exists';
  END IF;
  
  RETURN NEW;
END;
$$;

CREATE TRIGGER validate_post_before_insert
BEFORE INSERT OR UPDATE ON posts
FOR EACH ROW
EXECUTE FUNCTION validate_post();

Cascade Soft Deletes#

When a post is soft deleted, soft delete all comments:

CREATE OR REPLACE FUNCTION cascade_soft_delete_comments()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  IF NEW.deleted_at IS NOT NULL AND OLD.deleted_at IS NULL THEN
    UPDATE comments
    SET deleted_at = NEW.deleted_at
    WHERE post_id = NEW.id AND deleted_at IS NULL;
  END IF;
  
  RETURN NEW;
END;
$$;

CREATE TRIGGER cascade_post_soft_delete
AFTER UPDATE ON posts
FOR EACH ROW
WHEN (NEW.deleted_at IS NOT NULL AND OLD.deleted_at IS NULL)
EXECUTE FUNCTION cascade_soft_delete_comments();

Audit Trail for All Changes#

Log every change to sensitive tables:

-- Create audit log table
CREATE TABLE audit_logs (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  table_name TEXT NOT NULL,
  record_id UUID NOT NULL,
  action TEXT NOT NULL,
  old_data JSONB,
  new_data JSONB,
  user_id UUID,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Generic audit function
CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  INSERT INTO audit_logs (
    table_name,
    record_id,
    action,
    old_data,
    new_data,
    user_id
  ) VALUES (
    TG_TABLE_NAME,
    COALESCE(NEW.id, OLD.id),
    TG_OP,
    CASE WHEN TG_OP = 'DELETE' THEN row_to_json(OLD) ELSE NULL END,
    CASE WHEN TG_OP IN ('INSERT', 'UPDATE') THEN row_to_json(NEW) ELSE NULL END,
    auth.uid()
  );
  
  RETURN COALESCE(NEW, OLD);
END;
$$;

-- Attach to sensitive tables
CREATE TRIGGER audit_users
AFTER INSERT OR UPDATE OR DELETE ON profiles
FOR EACH ROW
EXECUTE FUNCTION audit_trigger();

CREATE TRIGGER audit_posts
AFTER INSERT OR UPDATE OR DELETE ON posts
FOR EACH ROW
EXECUTE FUNCTION audit_trigger();

Prevent Deletion of Referenced Records#

Block deletion if references exist:

CREATE OR REPLACE FUNCTION prevent_delete_if_referenced()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  IF EXISTS (SELECT 1 FROM comments WHERE post_id = OLD.id) THEN
    RAISE EXCEPTION 'Cannot delete post with existing comments';
  END IF;
  
  RETURN OLD;
END;
$$;

CREATE TRIGGER prevent_post_delete
BEFORE DELETE ON posts
FOR EACH ROW
EXECUTE FUNCTION prevent_delete_if_referenced();

Advanced Patterns#

Conditional Triggers#

Only execute trigger under certain conditions:

CREATE TRIGGER update_search_index
AFTER INSERT OR UPDATE ON posts
FOR EACH ROW
WHEN (NEW.published = TRUE) -- Only for published posts
EXECUTE FUNCTION update_search_index();

Trigger with Multiple Events#

CREATE TRIGGER maintain_post_stats
AFTER INSERT OR UPDATE OR DELETE ON comments
FOR EACH ROW
EXECUTE FUNCTION update_post_statistics();

Statement-Level Triggers#

Execute once per statement instead of per row:

CREATE TRIGGER log_bulk_delete
AFTER DELETE ON posts
FOR EACH STATEMENT
EXECUTE FUNCTION log_bulk_operation();

Useful for bulk operations where per-row triggers would be expensive.

Error Handling#

Handle errors gracefully:

CREATE OR REPLACE FUNCTION safe_increment_counter(post_id UUID)
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE posts
  SET view_count = view_count + 1
  WHERE id = post_id;
  
  RETURN TRUE;
EXCEPTION
  WHEN OTHERS THEN
    RAISE NOTICE 'Error incrementing counter: %', SQLERRM;
    RETURN FALSE;
END;
$$;

Log errors for debugging:

CREATE TABLE function_errors (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  function_name TEXT,
  error_message TEXT,
  error_detail TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION process_payment(amount DECIMAL)
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
BEGIN
  -- Payment processing logic
  
  RETURN TRUE;
EXCEPTION
  WHEN OTHERS THEN
    INSERT INTO function_errors (function_name, error_message, error_detail)
    VALUES ('process_payment', SQLERRM, SQLSTATE);
    
    RETURN FALSE;
END;
$$;

Testing Functions and Triggers#

Test functions directly in SQL Editor:

-- Test function
SELECT get_user_stats('user-uuid-here');

-- Test with different inputs
SELECT search_posts('nextjs');
SELECT search_posts('supabase');

-- Test error handling
SELECT safe_increment_counter('invalid-uuid');

Test triggers by performing operations:

-- Test insert trigger
INSERT INTO posts (title, content, user_id)
VALUES ('Test', 'Test content', auth.uid());

-- Verify trigger executed
SELECT * FROM audit_logs ORDER BY created_at DESC LIMIT 1;

-- Test update trigger
UPDATE posts SET title = 'Updated' WHERE id = 'post-uuid';

-- Test delete trigger
DELETE FROM posts WHERE id = 'post-uuid';

Common Pitfalls#

1. Infinite Trigger Loops#

-- ❌ Infinite loop
CREATE TRIGGER update_post
AFTER UPDATE ON posts
FOR EACH ROW
EXECUTE FUNCTION update_post_timestamp();

-- Function updates posts table, triggering itself again!

Solution: Use BEFORE triggers or check if value actually changed:

CREATE TRIGGER update_post
BEFORE UPDATE ON posts
FOR EACH ROW
WHEN (OLD.* IS DISTINCT FROM NEW.*)
EXECUTE FUNCTION update_post_timestamp();

2. Forgetting RETURN in Trigger Functions#

-- ❌ Missing RETURN
CREATE FUNCTION my_trigger()
RETURNS TRIGGER AS $$
BEGIN
  -- Do something
  -- Missing RETURN NEW or RETURN OLD
END;
$$ LANGUAGE plpgsql;

BEFORE triggers must return NEW (or NULL to cancel operation).

3. Using SECURITY DEFINER Without Validation#

-- ❌ Dangerous - no validation
CREATE FUNCTION delete_any_post(post_id UUID)
RETURNS VOID
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  DELETE FROM posts WHERE id = post_id;
END;
$$;

Always validate permissions in SECURITY DEFINER functions.

4. Not Handling NULL Values#

-- ❌ Fails on NULL
CREATE FUNCTION calculate_total(a INTEGER, b INTEGER)
RETURNS INTEGER AS $$
BEGIN
  RETURN a + b; -- NULL + 5 = NULL
END;
$$ LANGUAGE plpgsql;

-- ✅ Handle NULL
CREATE FUNCTION calculate_total(a INTEGER, b INTEGER)
RETURNS INTEGER AS $$
BEGIN
  RETURN COALESCE(a, 0) + COALESCE(b, 0);
END;
$$ LANGUAGE plpgsql;

Performance Considerations#

Functions and triggers add overhead. Optimize:

Keep functions simple: Complex logic slows down queries.

Avoid triggers on high-traffic tables: Each insert/update/delete executes the trigger.

Use statement-level triggers for bulk operations: More efficient than row-level.

Index columns used in function WHERE clauses: Functions still need indexes.

Cache function results when possible: Use STABLE or IMMUTABLE when appropriate.

-- IMMUTABLE: Always returns same result for same input
CREATE FUNCTION calculate_tax(amount DECIMAL)
RETURNS DECIMAL
IMMUTABLE
AS $$
BEGIN
  RETURN amount * 0.1;
END;
$$ LANGUAGE plpgsql;

-- STABLE: Same result within single query
CREATE FUNCTION get_current_user_id()
RETURNS UUID
STABLE
AS $$
BEGIN
  RETURN auth.uid();
END;
$$ LANGUAGE plpgsql;

Summary#

Postgres functions and triggers in Supabase enable:

• Automated workflows without application code
• Data consistency across multiple applications
• Complex validation at the database level
• Performance optimization through reduced round trips
• Audit trails and logging

Use them for data integrity and automation. Keep business logic in your application when it requires external APIs, complex testing, or frequent changes.

[INTERNAL LINK: supabase-authentication-authorization] [INTERNAL LINK: nextjs-supabase-database-design-optimization] [INTERNAL LINK: nextjs-supabase-security-best-practices]