Functions & Triggers

Note: This is mock/placeholder content for demonstration purposes.

Database functions and triggers enable server-side logic and automation.

Database Functions

Creating a Function

CREATE OR REPLACE FUNCTION get_user_projects(user_id UUID)
RETURNS TABLE (
  id UUID,
  name TEXT,
  created_at TIMESTAMPTZ
)
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  RETURN QUERY
  SELECT p.id, p.name, p.created_at
  FROM projects p
  INNER JOIN accounts_memberships am ON am.account_id = p.account_id
  WHERE am.user_id = get_user_projects.user_id;
END;
$$;

Calling from TypeScript

const { data, error } = await client.rpc('get_user_projects', {
  user_id: userId,
});

Common Function Patterns

Get User Accounts

CREATE OR REPLACE FUNCTION get_user_accounts(user_id UUID)
RETURNS TABLE (account_id UUID)
LANGUAGE sql
SECURITY DEFINER
AS $$
  SELECT account_id
  FROM accounts_memberships
  WHERE user_id = $1;
$$;

Check Permission

CREATE OR REPLACE FUNCTION has_permission(
  user_id UUID,
  account_id UUID,
  required_role TEXT
)
RETURNS BOOLEAN
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  user_role TEXT;
BEGIN
  SELECT role INTO user_role
  FROM accounts_memberships
  WHERE user_id = has_permission.user_id
    AND account_id = has_permission.account_id;

  RETURN user_role = required_role OR user_role = 'owner';
END;
$$;

Search Function

CREATE OR REPLACE FUNCTION search_projects(
  search_term TEXT,
  account_id UUID
)
RETURNS TABLE (
  id UUID,
  name TEXT,
  description TEXT,
  relevance REAL
)
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  RETURN QUERY
  SELECT
    p.id,
    p.name,
    p.description,
    ts_rank(
      to_tsvector('english', p.name || ' ' || COALESCE(p.description, '')),
      plainto_tsquery('english', search_term)
    ) AS relevance
  FROM projects p
  WHERE p.account_id = search_projects.account_id
    AND (
      to_tsvector('english', p.name || ' ' || COALESCE(p.description, ''))
      @@ plainto_tsquery('english', search_term)
    )
  ORDER BY relevance DESC;
END;
$$;

Triggers

Auto-Update 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 to table
CREATE TRIGGER update_projects_updated_at
  BEFORE UPDATE ON projects
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

Audit Log Trigger

-- Create audit log table
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 trigger function
CREATE OR REPLACE FUNCTION log_changes()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    INSERT INTO audit_log (table_name, record_id, action, new_data, user_id)
    VALUES (TG_TABLE_NAME, NEW.id, 'INSERT', to_jsonb(NEW), auth.uid());
    RETURN NEW;
  ELSIF TG_OP = 'UPDATE' THEN
    INSERT INTO audit_log (table_name, record_id, action, old_data, new_data, user_id)
    VALUES (TG_TABLE_NAME, NEW.id, 'UPDATE', to_jsonb(OLD), to_jsonb(NEW), auth.uid());
    RETURN NEW;
  ELSIF TG_OP = 'DELETE' THEN
    INSERT INTO audit_log (table_name, record_id, action, old_data, user_id)
    VALUES (TG_TABLE_NAME, OLD.id, 'DELETE', to_jsonb(OLD), auth.uid());
    RETURN OLD;
  END IF;
END;
$$;

-- Attach to table
CREATE TRIGGER audit_projects
  AFTER INSERT OR UPDATE OR DELETE ON projects
  FOR EACH ROW
  EXECUTE FUNCTION log_changes();

Cascade Soft Delete

CREATE OR REPLACE FUNCTION soft_delete_cascade()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  -- Soft delete related tasks
  UPDATE tasks
  SET deleted_at = NOW()
  WHERE project_id = OLD.id
    AND deleted_at IS NULL;

  RETURN OLD;
END;
$$;

CREATE TRIGGER soft_delete_project_tasks
  BEFORE DELETE ON projects
  FOR EACH ROW
  EXECUTE FUNCTION soft_delete_cascade();

Validation Triggers

Enforce Business Rules

CREATE OR REPLACE FUNCTION validate_project_budget()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  IF NEW.budget < 0 THEN
    RAISE EXCEPTION 'Budget cannot be negative';
  END IF;

  IF NEW.budget > 1000000 THEN
    RAISE EXCEPTION 'Budget cannot exceed 1,000,000';
  END IF;

  RETURN NEW;
END;
$$;

CREATE TRIGGER check_project_budget
  BEFORE INSERT OR UPDATE ON projects
  FOR EACH ROW
  EXECUTE FUNCTION validate_project_budget();

Prevent Orphaned Records

CREATE OR REPLACE FUNCTION prevent_owner_removal()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
  owner_count INTEGER;
BEGIN
  IF OLD.role = 'owner' THEN
    SELECT COUNT(*) INTO owner_count
    FROM accounts_memberships
    WHERE account_id = OLD.account_id
      AND role = 'owner'
      AND id != OLD.id;

    IF owner_count = 0 THEN
      RAISE EXCEPTION 'Cannot remove the last owner of an account';
    END IF;
  END IF;

  RETURN OLD;
END;
$$;

CREATE TRIGGER check_owner_before_delete
  BEFORE DELETE ON accounts_memberships
  FOR EACH ROW
  EXECUTE FUNCTION prevent_owner_removal();

Computed Columns

Virtual Column with Function

CREATE OR REPLACE FUNCTION project_task_count(project_id UUID)
RETURNS INTEGER
LANGUAGE sql
STABLE
AS $$
  SELECT COUNT(*)::INTEGER
  FROM tasks
  WHERE project_id = $1
    AND deleted_at IS NULL;
$$;

-- Use in queries
SELECT
  id,
  name,
  project_task_count(id) as task_count
FROM projects;

Event Notifications

Notify on Changes

CREATE OR REPLACE FUNCTION notify_project_change()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  PERFORM pg_notify(
    'project_changes',
    json_build_object(
      'operation', TG_OP,
      'record', NEW
    )::text
  );
  RETURN NEW;
END;
$$;

CREATE TRIGGER project_change_notification
  AFTER INSERT OR UPDATE ON projects
  FOR EACH ROW
  EXECUTE FUNCTION notify_project_change();

Listen in TypeScript

const channel = client
  .channel('project_changes')
  .on(
    'postgres_changes',
    {
      event: '*',
      schema: 'public',
      table: 'projects',
    },
    (payload) => {
      console.log('Project changed:', payload);
    }
  )
  .subscribe();

Security Functions

Row Level Security Helper

CREATE OR REPLACE FUNCTION is_account_member(account_id UUID)
RETURNS BOOLEAN
LANGUAGE sql
SECURITY DEFINER
STABLE
AS $$
  SELECT EXISTS (
    SELECT 1
    FROM accounts_memberships
    WHERE account_id = $1
      AND user_id = auth.uid()
  );
$$;

-- Use in RLS policy
CREATE POLICY "Users can access their account's projects"
  ON projects FOR ALL
  USING (is_account_member(account_id));

Scheduled Functions

Using pg_cron Extension

-- Enable pg_cron extension
CREATE EXTENSION IF NOT EXISTS pg_cron;

-- Schedule cleanup job
SELECT cron.schedule(
  'cleanup-old-sessions',
  '0 2 * * *', -- Every day at 2 AM
  $$
  DELETE FROM sessions
  WHERE expires_at < NOW();
  $$
);

Best Practices

1. Use SECURITY DEFINER carefully - Can bypass RLS
2. Add error handling - Use EXCEPTION blocks
3. Keep functions simple - One responsibility per function
4. Document functions - Add comments
5. Test thoroughly - Unit test database functions
6. Use STABLE/IMMUTABLE - Performance optimization
7. Avoid side effects - Make functions predictable
8. Return proper types - Use RETURNS TABLE for clarity

Testing Functions

-- Test function
DO $$
DECLARE
  result INTEGER;
BEGIN
  SELECT project_task_count('some-uuid') INTO result;

  ASSERT result >= 0, 'Task count should not be negative';

  RAISE NOTICE 'Test passed: task count = %', result;
END;
$$;

Debugging

Enable Function Logging

CREATE OR REPLACE FUNCTION debug_function()
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
  RAISE NOTICE 'Debug: Processing started';
  RAISE NOTICE 'Debug: Current user is %', auth.uid();
  -- Your function logic
  RAISE NOTICE 'Debug: Processing completed';
END;
$$;

Check Function Execution

-- View function execution stats
SELECT
  schemaname,
  funcname,
  calls,
  total_time,
  self_time
FROM pg_stat_user_functions
ORDER BY total_time DESC;