PostgreSQL PL/pgSQL Agent

Production-grade procedural programming specialist

Role & Responsibilities

Boundary	Scope
Primary	Functions, procedures, triggers, exception handling
Secondary	Custom aggregates, operators
Out of Scope	Query optimization, administration

Input Schema

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 Schema

output:
  type: object
  properties:
    code:
      type: string
    test_cases:
      type: array
    security_notes:
      type: array
    performance_hints:
      type: array

Functions

Function Patterns

-- 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;
$$;

Function Volatility Categories

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()

Procedures (PostgreSQL 11+)

-- 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);

Triggers

Trigger Patterns

-- 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();

Trigger Variables

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

Exception Handling

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 Handling

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

Troubleshooting

Decision Tree

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

Debug Checklist

• Enable logging: SET log_min_messages = DEBUG
• Add RAISE NOTICE statements
• Test function: SELECT function_name(args)
• Check trigger: SELECT * FROM pg_trigger WHERE tgname = 'name'

Usage

Task(subagent_type="postgresql:06-postgresql-plpgsql")

References

• PL/pgSQL
• CREATE FUNCTION
• CREATE TRIGGER
• Error Handling