plpgsql
PL/pgSQL 程序设计语言
概览
| ID | 扩展名 | Bin | Lib | Load | Create | Trust | Reloc | 模式 |
|---|
| 3280 | plpgsql | 否 | 是 | 否 | 是 | 是 | 否 | pg_catalog |
版本
| PG18 | PG17 | PG16 | PG15 | PG14 |
|---|
| 1.0 | 1.0 | 1.0 | 1.0 | 1.0 |
安装
提示：这是 PostgreSQL 内核自带的 contrib 扩展
CREATE EXTENSION plpgsql;
用法
plpgsql: PL/pgSQL 过程语言
PL/pgSQL 是 PostgreSQL 的默认过程语言。它在 SQL 基础上扩展了控制结构、变量、游标和异常处理等功能。
CREATE EXTENSION plpgsql; -- 默认已安装
-- 包含变量和控制流的基本函数
CREATE FUNCTION calculate_discount(price numeric, quantity integer) RETURNS numeric
LANGUAGE plpgsql AS $$
DECLARE
discount numeric := 0;
BEGIN
IF quantity >= 100 THEN
discount := 0.20;
ELSIF quantity >= 50 THEN
discount := 0.10;
ELSIF quantity >= 10 THEN
discount := 0.05;
END IF;
RETURN price * quantity * (1 - discount);
END;
$$;
-- 循环与集合返回函数
CREATE FUNCTION fibonacci(n integer) RETURNS SETOF integer
LANGUAGE plpgsql AS $$
DECLARE
a integer := 0;
b integer := 1;
tmp integer;
BEGIN
FOR i IN 1..n LOOP
RETURN NEXT a;
tmp := a + b;
a := b;
b := tmp;
END LOOP;
END;
$$;
SELECT * FROM fibonacci(10);
-- 异常处理
CREATE FUNCTION safe_divide(a numeric, b numeric) RETURNS numeric
LANGUAGE plpgsql AS $$
BEGIN
RETURN a / b;
EXCEPTION
WHEN division_by_zero THEN
RAISE NOTICE '除以零，返回 NULL';
RETURN NULL;
END;
$$;
-- 触发器函数
CREATE FUNCTION update_modified_column() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
NEW.modified_at = now();
RETURN NEW;
END;
$$;
CREATE TRIGGER set_modified
BEFORE UPDATE ON my_table
FOR EACH ROW EXECUTE FUNCTION update_modified_column();
-- 带事务控制的存储过程（PG 11+）
CREATE PROCEDURE batch_archive(batch_size integer)
LANGUAGE plpgsql AS $$
DECLARE
rows_moved integer;
BEGIN
LOOP
WITH moved AS (
DELETE FROM orders WHERE status = 'completed'
RETURNING *
)
INSERT INTO orders_archive SELECT * FROM moved;
GET DIAGNOSTICS rows_moved = ROW_COUNT;
COMMIT;
EXIT WHEN rows_moved < batch_size;
END LOOP;
END;
$$;