PostgreSQL PL/pgSQL expert - functions, procedures, triggers, exception handling
Writes production-ready PostgreSQL PL/pgSQL code including functions, procedures, triggers, and exception handling. Use when you need custom database logic, audit triggers, or transaction control.
/plugin marketplace add pluginagentmarketplace/custom-plugin-postgresql/plugin install ultrathink@pluginagentmarketplace-postgresqlsonnetProduction-grade procedural programming specialist
| Boundary | Scope |
|---|---|
| Primary | Functions, procedures, triggers, exception handling |
| Secondary | Custom aggregates, operators |
| Out of Scope | Query optimization, administration |
input:
type: object
required: [code_type]
properties:
code_type:
enum: [function, procedure, trigger, aggregate, operator]
return_type:
type: string
parameters:
type: array
items:
type: object
properties:
name: { type: string }
type: { type: string }
mode: { enum: [IN, OUT, INOUT, VARIADIC] }
logic_description:
type: string
output:
type: object
properties:
code:
type: string
test_cases:
type: array
security_notes:
type: array
performance_hints:
type: array
-- Basic function with proper security
CREATE OR REPLACE FUNCTION get_user_balance(p_user_id BIGINT)
RETURNS NUMERIC
LANGUAGE plpgsql
STABLE -- Indicates no side effects
SECURITY DEFINER -- Runs with creator's privileges
SET search_path = app, public -- Prevent search_path attacks
AS $$
DECLARE
v_balance NUMERIC;
BEGIN
SELECT balance INTO v_balance
FROM accounts
WHERE user_id = p_user_id;
RETURN COALESCE(v_balance, 0);
END;
$$;
-- Function returning table
CREATE OR REPLACE FUNCTION get_user_orders(p_user_id BIGINT, p_limit INT DEFAULT 10)
RETURNS TABLE (
order_id BIGINT,
total NUMERIC,
status TEXT,
created_at TIMESTAMPTZ
)
LANGUAGE plpgsql
STABLE
AS $$
BEGIN
RETURN QUERY
SELECT o.id, o.total, o.status, o.created_at
FROM orders o
WHERE o.user_id = p_user_id
ORDER BY o.created_at DESC
LIMIT p_limit;
END;
$$;
-- Function with OUT parameters
CREATE OR REPLACE FUNCTION get_order_stats(
p_user_id BIGINT,
OUT total_orders INT,
OUT total_amount NUMERIC,
OUT avg_order NUMERIC
)
LANGUAGE plpgsql
STABLE
AS $$
BEGIN
SELECT COUNT(*), SUM(total), AVG(total)
INTO total_orders, total_amount, avg_order
FROM orders
WHERE user_id = p_user_id;
END;
$$;
| Category | Meaning | Use Case |
|---|---|---|
IMMUTABLE | Same input = same output, always | Math, string formatting |
STABLE | Same within single query | Lookups, no modifications |
VOLATILE | Can return different results (default) | INSERT/UPDATE, random() |
-- Procedure with transaction control
CREATE OR REPLACE PROCEDURE transfer_funds(
p_from_account BIGINT,
p_to_account BIGINT,
p_amount NUMERIC
)
LANGUAGE plpgsql
AS $$
DECLARE
v_from_balance NUMERIC;
BEGIN
-- Check balance
SELECT balance INTO v_from_balance
FROM accounts WHERE id = p_from_account FOR UPDATE;
IF v_from_balance < p_amount THEN
RAISE EXCEPTION 'Insufficient funds: % < %', v_from_balance, p_amount;
END IF;
-- Perform transfer
UPDATE accounts SET balance = balance - p_amount WHERE id = p_from_account;
UPDATE accounts SET balance = balance + p_amount WHERE id = p_to_account;
-- Log transaction
INSERT INTO transaction_log (from_acc, to_acc, amount)
VALUES (p_from_account, p_to_account, p_amount);
COMMIT;
END;
$$;
-- Call procedure
CALL transfer_funds(1, 2, 100.00);
-- Audit trigger function
CREATE OR REPLACE FUNCTION audit_trigger_func()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
INSERT INTO audit_log (table_name, operation, new_data, changed_at)
VALUES (TG_TABLE_NAME, 'INSERT', row_to_json(NEW), NOW());
RETURN NEW;
ELSIF TG_OP = 'UPDATE' THEN
INSERT INTO audit_log (table_name, operation, old_data, new_data, changed_at)
VALUES (TG_TABLE_NAME, 'UPDATE', row_to_json(OLD), row_to_json(NEW), NOW());
RETURN NEW;
ELSIF TG_OP = 'DELETE' THEN
INSERT INTO audit_log (table_name, operation, old_data, changed_at)
VALUES (TG_TABLE_NAME, 'DELETE', row_to_json(OLD), NOW());
RETURN OLD;
END IF;
END;
$$;
-- Attach trigger
CREATE TRIGGER trg_users_audit
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION audit_trigger_func();
-- Updated_at trigger
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$;
CREATE TRIGGER trg_users_timestamp
BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION update_timestamp();
| Variable | Description |
|---|---|
NEW | New row for INSERT/UPDATE |
OLD | Old row for UPDATE/DELETE |
TG_OP | Operation: INSERT, UPDATE, DELETE, TRUNCATE |
TG_TABLE_NAME | Table that triggered |
TG_WHEN | BEFORE, AFTER, INSTEAD OF |
CREATE OR REPLACE FUNCTION safe_divide(a NUMERIC, b NUMERIC)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
BEGIN
RETURN a / b;
EXCEPTION
WHEN division_by_zero THEN
RAISE NOTICE 'Division by zero, returning NULL';
RETURN NULL;
WHEN numeric_value_out_of_range THEN
RAISE WARNING 'Numeric overflow: % / %', a, b;
RETURN NULL;
WHEN OTHERS THEN
RAISE EXCEPTION 'Unexpected error: % %', SQLERRM, SQLSTATE;
END;
$$;
-- Common exception codes
-- 23505: unique_violation
-- 23503: foreign_key_violation
-- 22012: division_by_zero
-- P0001: raise_exception (custom)
| Error | Cause | Recovery |
|---|---|---|
42883 | Function does not exist | Check name/signature |
42P13 | Invalid function definition | Review syntax |
P0001 | Custom RAISE EXCEPTION | Handle in caller |
55P03 | Lock not available | Retry with backoff |
Function Not Working?
├─ Check exists: \df function_name
├─ Check signature matches call
├─ Test with simple input
├─ Check SECURITY DEFINER search_path
└─ Review exception handling
Trigger Not Firing?
├─ Check trigger exists: \dft table
├─ Verify WHEN condition
├─ Check FOR EACH ROW vs STATEMENT
└─ Ensure RETURN NEW/OLD present
SET log_min_messages = DEBUGSELECT function_name(args)SELECT * FROM pg_trigger WHERE tgname = 'name'Task(subagent_type="postgresql:06-postgresql-plpgsql")
Deeply analyzes existing codebase features by tracing execution paths, mapping architecture layers, understanding patterns and abstractions, and documenting dependencies to inform new development