Functions, Triggers, and Stored Procedures

---

Moving Logic to the Database

While you can handle all logic in your application code, PostgreSQL allows you to run code directly in the database. This has significant advantages for certain use cases—especially in a Supabase context where security and performance matter.

---

Why Database Functions?

Benefits

1. Performance: Logic runs where data lives—no network round trips
2. Security: Functions can have elevated permissions (SECURITY DEFINER)
3. Consistency: Logic enforced regardless of how data is accessed
4. Atomicity: Operations within a function run in a transaction
5. Reusability: Call from triggers, RLS policies, or API

When to Use

• Validation: Complex business rules
• Computed values: Derived data that must be consistent
• Batch operations: Processing multiple rows efficiently
• Secure operations: Actions requiring elevated permissions
• Triggers: Automatic responses to data changes

---

PostgreSQL Functions

Basic Syntax

CREATE OR REPLACE FUNCTION function_name(parameters)
RETURNS return_type
LANGUAGE plpgsql  -- or sql
AS $$
  -- function body
$$;

Simple SQL Function

For straightforward queries:

-- Get user's post count
CREATE OR REPLACE FUNCTION get_post_count(user_uuid uuid)
RETURNS bigint
LANGUAGE sql
STABLE  -- Result depends only on inputs, doesn't modify data
AS $$
  SELECT COUNT(*)
  FROM posts
  WHERE user_id = user_uuid;
$$;

-- Usage
SELECT get_post_count('some-user-uuid');

PL/pgSQL Function

For complex logic:

CREATE OR REPLACE FUNCTION create_post_with_notification(
  p_title text,
  p_content text,
  p_user_id uuid
)
RETURNS uuid
LANGUAGE plpgsql
AS $$
DECLARE
  new_post_id uuid;
  follower_id uuid;
BEGIN
  -- Insert the post
  INSERT INTO posts (title, content, user_id)
  VALUES (p_title, p_content, p_user_id)
  RETURNING id INTO new_post_id;

  -- Notify all followers
  FOR follower_id IN
    SELECT followed_by FROM follows WHERE following = p_user_id
  LOOP
    INSERT INTO notifications (user_id, type, post_id)
    VALUES (follower_id, 'new_post', new_post_id);
  END LOOP;

  RETURN new_post_id;
END;
$$;

---

Function Volatility

Tell PostgreSQL about your function's behavior:

-- IMMUTABLE: Same inputs always produce same output, no database access
CREATE FUNCTION add(a int, b int) RETURNS int
IMMUTABLE
AS $$ SELECT a + b $$ LANGUAGE sql;

-- STABLE: Same inputs produce same output within a transaction, reads database
CREATE FUNCTION get_user_name(user_id uuid) RETURNS text
STABLE
AS $$ SELECT name FROM users WHERE id = user_id $$ LANGUAGE sql;

-- VOLATILE: May return different results, may modify database (default)
CREATE FUNCTION update_timestamp() RETURNS trigger
VOLATILE
AS $$ ... $$ LANGUAGE plpgsql;

Why it matters: PostgreSQL uses this for query optimization. Marking functions correctly allows better caching and parallel execution.

---

SECURITY DEFINER vs SECURITY INVOKER

SECURITY INVOKER (Default)

Function runs with the permissions of the user calling it:

CREATE FUNCTION get_my_posts()
RETURNS SETOF posts
LANGUAGE sql
SECURITY INVOKER  -- Default
AS $$
  SELECT * FROM posts WHERE user_id = auth.uid();
$$;

-- RLS policies apply based on the calling user

SECURITY DEFINER

Function runs with the permissions of the function creator:

CREATE FUNCTION admin_delete_user(target_user_id uuid)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER  -- Runs with elevated permissions
SET search_path = public  -- Security best practice
AS $$
BEGIN
  -- This bypasses RLS because the function creator has full access
  DELETE FROM profiles WHERE id = target_user_id;
  DELETE FROM auth.users WHERE id = target_user_id;
END;
$$;

-- Revoke direct access, only allow through function
REVOKE ALL ON FUNCTION admin_delete_user FROM PUBLIC;
GRANT EXECUTE ON FUNCTION admin_delete_user TO admin_role;

Warning: SECURITY DEFINER functions can be dangerous. Always:

• Set search_path explicitly
• Revoke unnecessary permissions
• Validate inputs carefully

---

Triggers

Triggers automatically execute functions in response to table events.

Trigger Events

• INSERT: New row added
• UPDATE: Existing row modified
• DELETE: Row removed
• TRUNCATE: Table truncated

Trigger Timing

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

Basic Trigger Pattern

-- Step 1: Create the trigger function
CREATE OR REPLACE FUNCTION update_modified_at()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;  -- Return modified row
END;
$$;

-- Step 2: Attach trigger to table
CREATE TRIGGER posts_updated_at
  BEFORE UPDATE ON posts
  FOR EACH ROW
  EXECUTE FUNCTION update_modified_at();

Common Trigger Use Cases

Auto-Update Timestamps

CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS trigger AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Apply to multiple tables
CREATE TRIGGER update_posts_modtime
  BEFORE UPDATE ON posts
  FOR EACH ROW EXECUTE FUNCTION update_modified_column();

CREATE TRIGGER update_comments_modtime
  BEFORE UPDATE ON comments
  FOR EACH ROW EXECUTE FUNCTION update_modified_column();

Audit Logging

CREATE TABLE audit_log (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  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()
);

CREATE OR REPLACE FUNCTION audit_trigger_func()
RETURNS trigger AS $$
BEGIN
  INSERT INTO audit_log (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 IN ('UPDATE', 'DELETE') THEN to_jsonb(OLD) END,
    CASE WHEN TG_OP IN ('INSERT', 'UPDATE') THEN to_jsonb(NEW) END,
    auth.uid()
  );

  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

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

Create Profile on User Signup

-- Supabase pattern: create profile when user signs up
CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS trigger AS $$
BEGIN
  INSERT INTO public.profiles (id, email, full_name, avatar_url)
  VALUES (
    NEW.id,
    NEW.email,
    NEW.raw_user_meta_data->>'full_name',
    NEW.raw_user_meta_data->>'avatar_url'
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION handle_new_user();

Prevent Certain Updates

CREATE OR REPLACE FUNCTION prevent_email_change()
RETURNS trigger AS $$
BEGIN
  IF OLD.email <> NEW.email THEN
    RAISE EXCEPTION 'Email cannot be changed';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER no_email_change
  BEFORE UPDATE ON users
  FOR EACH ROW EXECUTE FUNCTION prevent_email_change();

---

Calling Functions from Supabase

RPC (Remote Procedure Call)

// Call a function that returns data
const { data, error } = await supabase
  .rpc('get_post_count', { user_uuid: userId })

// Call a function that performs an action
const { data, error } = await supabase
  .rpc('create_post_with_notification', {
    p_title: 'Hello',
    p_content: 'World',
    p_user_id: userId
  })

Function Must Be Accessible

-- Grant execute permission to authenticated users
GRANT EXECUTE ON FUNCTION get_post_count TO authenticated;

-- Or to anonymous users
GRANT EXECUTE ON FUNCTION public_function TO anon;

---

Stored Procedures

PostgreSQL 11+ supports procedures (different from functions):

CREATE OR REPLACE PROCEDURE transfer_money(
  sender_id uuid,
  receiver_id uuid,
  amount numeric
)
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE accounts SET balance = balance - amount WHERE id = sender_id;
  UPDATE accounts SET balance = balance + amount WHERE id = receiver_id;
  COMMIT;  -- Procedures can control transactions
END;
$$;

-- Call with CALL
CALL transfer_money('sender-uuid', 'receiver-uuid', 100.00);

Key Difference: Procedures can contain transaction control (COMMIT, ROLLBACK), functions cannot.

---

Best Practices

1. Keep Functions Focused

-- Good: Single responsibility
CREATE FUNCTION get_user_email(user_id uuid) RETURNS text ...
CREATE FUNCTION send_notification(user_id uuid, message text) RETURNS void ...

-- Bad: Too many responsibilities
CREATE FUNCTION do_everything(user_id uuid) RETURNS void ...

2. Use Appropriate Volatility

-- Allows PostgreSQL to optimize
CREATE FUNCTION is_admin(user_id uuid) RETURNS boolean
STABLE  -- Same result within a transaction
AS $$ ... $$;

3. Handle Errors Properly

CREATE OR REPLACE FUNCTION safe_transfer(...)
RETURNS boolean AS $$
BEGIN
  -- ... logic ...

  RETURN true;
EXCEPTION
  WHEN insufficient_privilege THEN
    RAISE NOTICE 'Permission denied';
    RETURN false;
  WHEN OTHERS THEN
    RAISE NOTICE 'Error: %', SQLERRM;
    RETURN false;
END;
$$ LANGUAGE plpgsql;

4. Be Careful with SECURITY DEFINER

CREATE FUNCTION dangerous_function()
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, pg_temp  -- Prevent search path attacks
AS $$
BEGIN
  -- Validate inputs before doing anything
  IF NOT is_authorized() THEN
    RAISE EXCEPTION 'Unauthorized';
  END IF;
  -- ... logic ...
END;
$$;

---

Key Takeaways

1. Functions encapsulate logic: Run code where data lives
2. Triggers automate responses: React to data changes automatically
3. SECURITY DEFINER with caution: Powerful but potentially dangerous
4. Volatility matters: Help PostgreSQL optimize
5. RPC exposes functions: Call from your application via Supabase
6. Audit with triggers: Track all changes automatically

---

Module Summary

In this module, you've built a solid PostgreSQL foundation:

• Why PostgreSQL powers Supabase
• Data types and when to use them
• Modeling relationships with foreign keys
• Optimizing queries with indexes
• Extending behavior with functions and triggers

This knowledge applies directly to building effective Supabase applications. Next, we'll explore authentication and how Supabase manages user identity.

---

The database is not just storage—it's a programmable platform. Moving logic to where data lives often produces simpler, faster, more secure applications.