postgresql-plpgsql

name	postgresql-plpgsql
description	Write PL/pgSQL - functions, procedures, triggers, error handling
version	3.0.0
sasmp_version	1.3.0
bonded_agent	06-postgresql-plpgsql
bond_type	PRIMARY_BOND
category	database
difficulty	advanced
estimated_time	4h

PostgreSQL PL/pgSQL Skill

Atomic skill for procedural programming

Overview

Production-ready patterns for functions, procedures, triggers, and exception handling.

Prerequisites

• PostgreSQL 16+
• Understanding of SQL

Parameters

parameters:
  code_type:
    type: string
    required: true
    enum: [function, procedure, trigger, aggregate]
  volatility:
    type: string
    enum: [IMMUTABLE, STABLE, VOLATILE]

Quick Reference

Function Template

CREATE OR REPLACE FUNCTION func_name(p_param TYPE)
RETURNS return_type
LANGUAGE plpgsql STABLE SECURITY DEFINER
SET search_path = app, public
AS $$ DECLARE v_result TYPE; BEGIN
    -- Logic
    RETURN v_result;
EXCEPTION WHEN OTHERS THEN
    RAISE WARNING '%', SQLERRM;
    RETURN NULL;
END; $$;

Trigger Template

CREATE OR REPLACE FUNCTION trigger_func() RETURNS TRIGGER AS $$

    IF TG_OP    NEW.updated_at : NOW();  IF;
     ;
; $$  plpgsql;

  trg_name BEFORE   t      trigger_func();