Functions & Triggers

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;