postgresql-plpgsql

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

PostgreSQL PL/pgSQL Skill

PostgreSQL PL/pgSQL 技能

Atomic skill for procedural programming

用于过程式编程的原子技能

Overview

概述

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

适用于函数、存储过程、触发器和异常处理的生产就绪型模式。

Prerequisites

前置要求

PostgreSQL 16+
Understanding of SQL

PostgreSQL 16+
具备SQL基础知识

Parameters

参数

yaml

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

yaml

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

Quick Reference

快速参考

Function Template

函数模板

sql

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

sql

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

触发器模板

sql

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

sql

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

分类	使用场景
IMMUTABLE	数学运算、格式化
STABLE	查询操作（无写入）
VOLATILE	插入操作、random()

Exception Handling

异常处理

sql

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

sql

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

错误	原因	解决方案
`42883`	函数未找到	检查签名
`42P13`	定义无效	检查语法
触发器未触发	时机错误	检查BEFORE/AFTER设置

Usage

使用方法

Skill("postgresql-plpgsql")

Skill("postgresql-plpgsql")