postgresql-plpgsql

pluginagentmarketplace's avatarfrom pluginagentmarketplace

Write PL/pgSQL - functions, procedures, triggers, error handling

⭐1stars🔀0forks📁View on GitHub🕐Updated Jan 5, 2026
[SQL and Databases]

When & Why to Use This Skill

This Claude skill specializes in generating production-ready PostgreSQL PL/pgSQL code, including functions, procedures, and triggers. It streamlines database development by providing optimized templates for complex procedural logic, robust error handling, and security-focused configurations, ensuring high-performance and maintainable database schemas.

Use Cases

  • Database Automation: Creating triggers to automate data synchronization, audit logging, and automatic timestamp updates across relational tables.
  • Business Logic Encapsulation: Developing complex stored procedures and functions to handle multi-row transactions and server-side data processing directly within PostgreSQL.
  • Performance Tuning: Generating optimized functions with specific volatility settings (IMMUTABLE, STABLE, VOLATILE) to improve query planner efficiency and reduce execution overhead.
  • Error Management & Resilience: Implementing comprehensive exception handling patterns to manage database constraints, unique violations, and runtime errors gracefully.
namepostgresql-plpgsql
descriptionWrite PL/pgSQL - functions, procedures, triggers, error handling
version"3.0.0"
sasmp_version"1.3.0"
bonded_agent06-postgresql-plpgsql
bond_typePRIMARY_BOND
categorydatabase
difficultyadvanced
estimated_time4h

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 $$
BEGIN
    IF TG_OP = 'UPDATE' THEN NEW.updated_at := NOW(); END IF;
    RETURN NEW;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER trg_name BEFORE UPDATE ON t FOR EACH ROW EXECUTE FUNCTION trigger_func();

Volatility

Category Use Case
IMMUTABLE Math, formatting
STABLE Lookups (no writes)
VOLATILE INSERT, random()

Exception Handling

EXCEPTION
    WHEN unique_violation THEN ...  -- 23505
    WHEN foreign_key_violation THEN ...  -- 23503
    WHEN OTHERS THEN RAISE EXCEPTION '% [%]', SQLERRM, SQLSTATE;

Troubleshooting

Error Cause Solution
42883 Function not found Check signature
42P13 Invalid definition Review syntax
Trigger not firing Wrong timing Check BEFORE/AFTER

Usage

Skill("postgresql-plpgsql")