Functions & Triggers

PostgreSQL Functions trong Supabase

Tại sao dùng Functions?

┌─────────────────────────────────────────────────────────────┐
│                    WHY DATABASE FUNCTIONS?                   │
├─────────────────────────────────────────────────────────────┤
│                                                              │
│  1. PERFORMANCE                                              │
│     - Chạy trong database (không network latency)           │
│     - Batch operations nhanh hơn                            │
│                                                              │
│  2. SECURITY                                                 │
│     - SECURITY DEFINER bypasses RLS                         │
│     - Complex authorization logic                           │
│                                                              │
│  3. REUSABILITY                                              │
│     - Dùng lại trong triggers, RLS policies                 │
│     - Call từ client via RPC                                │
│                                                              │
│  4. DATA INTEGRITY                                           │
│     - Validate complex business rules                       │
│     - Atomic transactions                                   │
│                                                              │
└─────────────────────────────────────────────────────────────┘

---

Function Basics

Simple Function

-- Basic function
CREATE OR REPLACE FUNCTION hello_world()
RETURNS TEXT AS $$
BEGIN
  RETURN 'Hello, World!';
END;
$$ LANGUAGE plpgsql;

-- Call it
SELECT hello_world(); -- 'Hello, World!'

Function với Parameters

-- Function with parameters
CREATE OR REPLACE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER AS $$
BEGIN
  RETURN a + b;
END;
$$ LANGUAGE plpgsql;

-- Call it
SELECT add_numbers(5, 3); -- 8

Function trả về Table

-- Return multiple rows
CREATE OR REPLACE FUNCTION get_active_projects(user_uuid UUID)
RETURNS TABLE (
  id UUID,
  name TEXT,
  task_count BIGINT
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    p.id,
    p.name,
    COUNT(t.id)::BIGINT as task_count
  FROM projects p
  LEFT JOIN tasks t ON t.project_id = p.id
  WHERE p.owner_id = user_uuid
  GROUP BY p.id, p.name;
END;
$$ LANGUAGE plpgsql STABLE;

---

SECURITY DEFINER vs INVOKER

Sự khác biệt

-- SECURITY INVOKER (default)
-- Chạy với quyền của người gọi
-- RLS policies áp dụng
CREATE FUNCTION my_function()
RETURNS void AS $$
  ...
$$ LANGUAGE plpgsql SECURITY INVOKER;

-- SECURITY DEFINER
-- Chạy với quyền của người tạo function (thường là postgres)
-- BYPASS RLS
CREATE FUNCTION admin_function()
RETURNS void AS $$
  ...
$$ LANGUAGE plpgsql SECURITY DEFINER;

Khi nào dùng SECURITY DEFINER?

-- ✅ USE CASE: Auto-create profile sau signup
CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  -- Cần bypass RLS để INSERT vào profiles
  INSERT INTO public.profiles (id, email)
  VALUES (NEW.id, NEW.email);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- ✅ USE CASE: Admin operations
CREATE OR REPLACE FUNCTION admin_delete_user(target_user_id UUID)
RETURNS void AS $$
BEGIN
  -- Check if caller is admin first
  IF NOT is_admin(auth.uid()) THEN
    RAISE EXCEPTION 'Permission denied';
  END IF;

  -- Bypass RLS to delete
  DELETE FROM profiles WHERE id = target_user_id;
  DELETE FROM auth.users WHERE id = target_user_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

---

Triggers

Trigger là gì?

┌─────────────────────────────────────────────────────────────┐
│                       TRIGGER FLOW                           │
├─────────────────────────────────────────────────────────────┤
│                                                              │
│  INSERT/UPDATE/DELETE ──▶ BEFORE Trigger ──▶ Operation      │
│                                               │              │
│                                               ▼              │
│                                          AFTER Trigger       │
│                                                              │
│  Timing: BEFORE or AFTER                                    │
│  Events: INSERT, UPDATE, DELETE                             │
│  Level: FOR EACH ROW or FOR EACH STATEMENT                  │
│                                                              │
└─────────────────────────────────────────────────────────────┘

Trigger Variables

-- Special variables trong trigger function
NEW   -- Row mới (INSERT, UPDATE)
OLD   -- Row cũ (UPDATE, DELETE)
TG_OP -- Operation: 'INSERT', 'UPDATE', 'DELETE'

---

Common Trigger Patterns

1. Auto-update Timestamps

-- Sử dụng extension moddatetime
CREATE TRIGGER set_updated_at
  BEFORE UPDATE ON tasks
  FOR EACH ROW
  EXECUTE FUNCTION extensions.moddatetime(updated_at);

-- Hoặc tự viết
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_timestamp
  BEFORE UPDATE ON tasks
  FOR EACH ROW
  EXECUTE FUNCTION update_timestamp();

2. Auto-create Profile

CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO public.profiles (id, email, full_name)
  VALUES (
    NEW.id,
    NEW.email,
    NEW.raw_user_meta_data->>'full_name'
  );
  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();

---

3. Audit Log

-- Audit 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,
  changed_by UUID,
  changed_at TIMESTAMPTZ DEFAULT NOW()
);

-- Generic audit function
CREATE OR REPLACE FUNCTION audit_changes()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_log (table_name, record_id, action, old_data, new_data, changed_by)
  VALUES (
    TG_TABLE_NAME,
    COALESCE(NEW.id, OLD.id),
    TG_OP,
    CASE WHEN TG_OP != 'INSERT' THEN to_jsonb(OLD) END,
    CASE WHEN TG_OP != 'DELETE' THEN to_jsonb(NEW) END,
    auth.uid()
  );

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

-- Apply to table
CREATE TRIGGER audit_tasks
  AFTER INSERT OR UPDATE OR DELETE ON tasks
  FOR EACH ROW
  EXECUTE FUNCTION audit_changes();

---

4. Notify External Service

-- Notify via pg_notify (for Realtime or external consumers)
CREATE OR REPLACE FUNCTION notify_task_change()
RETURNS TRIGGER AS $$
BEGIN
  PERFORM pg_notify(
    'task_changes',
    json_build_object(
      'action', TG_OP,
      'task_id', COALESCE(NEW.id, OLD.id),
      'project_id', COALESCE(NEW.project_id, OLD.project_id)
    )::text
  );
  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER on_task_change
  AFTER INSERT OR UPDATE OR DELETE ON tasks
  FOR EACH ROW
  EXECUTE FUNCTION notify_task_change();

5. Enqueue to Message Queue

-- Send to pgmq on specific events
CREATE OR REPLACE FUNCTION enqueue_notification()
RETURNS TRIGGER AS $$
BEGIN
  -- Enqueue email notification
  PERFORM pgmq.send(
    'email_queue',
    jsonb_build_object(
      'type', 'task_assigned',
      'task_id', NEW.id,
      'assignee_id', NEW.assignee_id
    )
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Only trigger when assignee changes
CREATE TRIGGER on_task_assigned
  AFTER UPDATE ON tasks
  FOR EACH ROW
  WHEN (OLD.assignee_id IS DISTINCT FROM NEW.assignee_id AND NEW.assignee_id IS NOT NULL)
  EXECUTE FUNCTION enqueue_notification();

---

Call Functions từ Client

Supabase RPC

// Simple function call
const { data, error } = await supabase.rpc('hello_world');

// With parameters
const { data, error } = await supabase.rpc('add_numbers', {
  a: 5,
  b: 3
});

// Function returning rows
const { data: projects } = await supabase.rpc('get_active_projects', {
  user_uuid: userId
});

Complex Business Logic

-- Transaction with multiple operations
CREATE OR REPLACE FUNCTION create_project_with_tasks(
  project_name TEXT,
  task_titles TEXT[]
)
RETURNS UUID AS $$
DECLARE
  new_project_id UUID;
BEGIN
  -- Create project
  INSERT INTO projects (name, owner_id)
  VALUES (project_name, auth.uid())
  RETURNING id INTO new_project_id;

  -- Create tasks
  INSERT INTO tasks (project_id, title)
  SELECT new_project_id, unnest(task_titles);

  RETURN new_project_id;
END;
$$ LANGUAGE plpgsql SECURITY INVOKER;

// Call from client
const { data: projectId } = await supabase.rpc('create_project_with_tasks', {
  project_name: 'New Project',
  task_titles: ['Task 1', 'Task 2', 'Task 3']
});

---

Function Attributes

Performance Hints

-- STABLE: Function không modify data, same input = same output trong transaction
CREATE FUNCTION get_user_count()
RETURNS BIGINT AS $$
  SELECT COUNT(*) FROM profiles;
$$ LANGUAGE sql STABLE;

-- IMMUTABLE: Pure function, same input = same output luôn
CREATE FUNCTION calculate_tax(amount NUMERIC)
RETURNS NUMERIC AS $$
  SELECT amount * 0.1;
$$ LANGUAGE sql IMMUTABLE;

-- VOLATILE (default): Có thể modify data hoặc return khác nhau mỗi lần
CREATE FUNCTION insert_log(msg TEXT)
RETURNS void AS $$
  INSERT INTO logs (message) VALUES (msg);
$$ LANGUAGE sql VOLATILE;

Usage in Indexes

-- IMMUTABLE functions có thể dùng trong index
CREATE INDEX idx_lower_email ON users (lower(email));
-- lower() là immutable

---

Debugging Functions

RAISE for Logging

CREATE OR REPLACE FUNCTION debug_example()
RETURNS void AS $$
BEGIN
  RAISE DEBUG 'Starting function';
  RAISE NOTICE 'Processing...';
  RAISE WARNING 'This might be slow';
  RAISE EXCEPTION 'Something went wrong!'; -- Aborts transaction
END;
$$ LANGUAGE plpgsql;

View Logs

-- In Supabase Dashboard > Logs > Postgres
-- Or via SQL
SELECT * FROM postgres_logs
WHERE event_message LIKE '%debug_example%'
ORDER BY timestamp DESC;

---

Tổng kết

Function Best Practices

1. Naming: verb_noun format (get_active_projects, create_task)
2. SECURITY DEFINER: Chỉ khi cần bypass RLS
3. Attributes: Set STABLE/IMMUTABLE khi có thể
4. Error handling: Use RAISE EXCEPTION với clear messages
5. Input validation: Validate parameters ngay đầu function

Trigger Best Practices

1. Keep it fast: Trigger chạy synchronously
2. Avoid loops: Trigger A → Trigger B → Trigger A
3. Use WHEN clause: Filter events cần handle
4. SECURITY DEFINER: Cho operations cần elevated privileges

Common Patterns

Pattern	Implementation
Auto timestamp	moddatetime trigger
Auto-create related	AFTER INSERT trigger
Audit log	AFTER trigger with JSONB
Enqueue job	Trigger → pgmq.send()
Notify realtime	pg_notify()

---

Q&A

1. Có business logic nào nên move vào function?
2. Đã dùng triggers trước đây chưa?
3. Vấn đề gì với audit logging?