目录

源码安装

cd /home/zmy/software/postgresql/postgresql-18.3
apt install -y gcc pkg-config libicu-dev bison flex libreadline-dev zlib1g-dev libssl-dev libxml2-dev libxslt1-dev libsystemd-dev make docbook-xml docbook-xsl libxml2-utils xsltproc fop
# 编译
./configure \
  --prefix=/home/zmy/software/postgresql/install \
  --with-pgport=15432 \
  --with-ssl=openssl \
  --with-systemd \
  --with-libxml \
  --with-libxslt \
  --with-liburing \ # 启用io_uring
  --with-lz4 \
  --with-zstd
# 构建：构建所有可构建的内容，包括文档（HTML 和手册页）以及附加模块 （contrib）
make world -j$(nproc)
# 安装
make install-world
# 共享库
sudo ldconfig /home/zmy/software/postgresql/install/lib/
# 设置环境变量 ~/.bashrc
export PGHOME=/home/zmy/software/postgresql/install
export PGDATA=$PGHOME/data
export PATH=$PGHOME/bin:$PATH
export MANPATH=$PGHOME/share/man:$MANPATH
. ~/.bashrc
# 初始化数据库
pg_ctl initdb # 或者 initdb （-D /usr/local/pgsql/data 或者设置PGDATA环境变量 -W 交互式设置密码）
# 启动数据库
pg_ctl start -l logfile # 或者 postgres -D /usr/local/pgsql/data > logfile 2>&1 &
# 停止数据库
pg_ctl stop -m smart
# 配置为服务
sudo sh -c 'cat > /etc/systemd/system/postgresql.service << EOF
[Unit]
Description=PostgreSQL database server
After=network-online.target
Wants=network-online.target

[Service]
Type=notify
User=zmy
ExecStart=/home/zmy/software/postgresql/install/bin/postgres -D /home/zmy/software/postgresql/install/data
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=infinity

[Install]
WantedBy=multi-user.target
EOF'
sudo systemctl daemon-reload
sudo systemctl enable
# 安装官方扩展(可选)
cd /home/zmy/software/postgresql/postgresql-18.3/contrib
make && make install
# 设置密码
ALTER USER zmy WITH PASSWORD '123456';
# 配置免密
cat > ~/.pgpass <<EOF
# hostname:port:database:username:password
# 前四项可以使用通配符 *
localhost:5432:postgres:zmy:123456
127.0.0.1:5432:postgres:zmy:123456
EOF
chmod 0600 ~/.pgpass

插件安装

pgBouncer

# 准备环境
curl -O https://www.pgbouncer.org/downloads/files/1.25.2/pgbouncer-1.25.2.tar.gz
tar zxf pgbouncer-1.25.2.tar.gz && cd pgbouncer-1.25.2
sudo apt install libevent-dev python3 pandoc -y
# 构建
./configure \
    --prefix=/home/zmy/software/postgresql/other_ext/pgbouncer/install \
    --with-systemd
# 编译
make -j$(nproc)
# 安装
make install
ln -sf /home/zmy/software/postgresql/other_ext/pgbouncer/install/bin/pgbouncer /usr/local/bin/pgbouncer
# 配置
mkdir /home/zmy/software/postgresql/other_ext/pgbouncer/install{etc,log,run,data}
# 连接pg，查看所有用户和密码哈希
SELECT 
    usename AS username,
    passwd AS password_hash
FROM pg_shadow
WHERE usename NOT LIKE 'pg_%'
  AND passwd IS NOT NULL
ORDER BY usename;

# 认证文件
cat > etc/userlist.txt <<EOF
;;;
;;; PgBouncer 用户认证文件
;;; 格式: "用户名" "密码哈希"
;;;
;;; 密码哈希可以是:
;;; - MD5: md5 + md5(password + username)
;;; - SCRAM-SHA-256: SCRAM-SHA-256$...
;;; - 明文: password（不推荐）
;;;

;; [修改] 根据上一步查询结果填写
"postgres" "md5abcdef1234567890abcdef1234567890"
"myuser" "md5d8578edf8458ce06fbc5bb76a58c5ca4"

;; 添加 PgBouncer 管理员用户（用于管理控制台）
;; 如果 postgres 用户已添加，可以用它作为管理员
EOF

cat > etc/pgbouncer.ini <<EOF
[databases]
postgres = host=localhost port=5432 user=postgres password=123456
uni_dev_platform = host=localhost port=5432 user=unicorn password=123456

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = scram-sha-256
auth_file = /home/zmy/software/postgresql/other_ext/pgbouncer/install/etc/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 50
max_db_connections = 150
logfile = /home/zmy/software/postgresql/other_ext/pgbouncer/install/log/pgbouncer.log
pidfile = /home/zmy/software/postgresql/other_ext/pgbouncer/install/run/pgbouncer.pid
# admin_users 可以连接虚拟的pgbouncer数据库，执行一些特殊的 SHOW 和 RELOAD 命令
admin_users = zmy
EOF

# 配置服务 share/doc/pgbouncer/pgbouncer.service
sudo sh -c 'cat > /etc/systemd/system/pgbouncer.service <<EOF
[Unit]
Description=connection pooler for PostgreSQL
Documentation=https://www.pgbouncer.org/
After=network.target postgresql.service
Wants=network-online.target
#Requires=pgbouncer.socket

[Service]
Type=forking
User=zmy
Group=zmy
ExecStart=/usr/local/bin/pgbouncer -d /home/zmy/software/postgresql/other_ext/pgbouncer/install/etc/pgbouncer.ini
ExecReload=/bin/kill -HUP $MAINPID
PIDFile=/home/zmy/software/postgresql/other_ext/pgbouncer/install/run/pgbouncer.pid
KillSignal=SIGINT
Restart=on-failure

# 安全增强 (Hardening)
ProtectSystem=strict
ReadWritePaths=/home/zmy/software/postgresql/other_ext/pgbouncer/install/run /home/zmy/software/postgresql/other_ext/pgbouncer/install/log /home/zmy/software/postgresql/other_ext/pgbouncer/install/etc
NoNewPrivileges=true
PrivateTmp=true

LimitNOFILE=65536

[Install]
WantedBy=multi-user.target
EOF'

pgvector

# 下载源码
git clone --branch v0.8.2 https://github.com/pgvector/pgvector.git
cd pgvector
# 编译安装
make PG_CONFIG=/home/zmy/software/postgresql/install/bin/pg_config
make PG_CONFIG=/home/zmy/software/postgresql/install/bin/pg_config install
# 重启
sudo systemctl restart postgresql
# 安装扩展
CREATE EXTENSION vector;

zhparser

# 安装scws
curl -LO http://www.xunsearch.com/scws/down/scws-1.2.3.tar.bz2
tar xf scws-1.2.3.tar.bz2
cd scws-1.2.3
./configure
make install
# 编译安装zhparser
git clone https://github.com/amutu/zhparser.git
PG_CONFIG=/home/zmy/software/postgresql/install/bin/pg_con make && make install

CREATE EXTENSION zhparser;
CREATE TEXT SEARCH CONFIGURATION chinese_zh (PARSER = zhparser);
ALTER TEXT SEARCH CONFIGURATION chinese_zh ADD MAPPING FOR n,v,a,i,e,l WITH simple;
--创建bm25索引
--K1 - 项频率饱和参数（默认为 1.2）值越大，词频对得分的影响持续越长
--b - 长度归一化参数（默认为 0.75）
CREATE INDEX docs_idx ON documents USING bm25(content) WITH (text_config='chinese_zh', k1=1.2, b=0.75);

pg_jieba

git clone https://github.com/jaiminpan/pg_jieba
cd pg_jieba
# initilized sub-project
git submodule update --init --recursive

cd pg_jieba
mkdir build && cd build
cmake -DCMAKE_PREFIX_PATH=/home/zmy/software/postgresql/install ..

make
make install

相关概念

主要角色

权限相关

-- 查看当前的搜索路径
SHOW search_path;
-- 临时设置
SET search_path TO myschema, public;
RESET search_path;
-- 为当前用户设置
ALTER ROLE myuser SET search_path TO "$user", myschema, public;
-- 为整个数据库设置
ALTER DATABASE mydb SET search_path TO "$user", myschema, public;
-- 为整个示例设置
search_path = 'myschema, public'
# JDBC中指定搜索路径
String url = "jdbc:postgresql://127.0.0.1:5432/test_db?options=-c%20search_path%3Dtest_schema,public,$user";

配置文件

触发器

CREATE TRIGGER trigger_name
    { BEFORE | AFTER | INSTEAD OF } 
    { INSERT | UPDATE [OF column_name [, ...]] | DELETE | TRUNCATE }
    ON table_name
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    EXECUTE FUNCTION function_name();

官方文档

Chapter 4. SQL语法

4.1 词法结构

4.2 值表达式

4.3. 调用函数

Chapter 5. 数据定义

5.3. 标识列

5.4 生成列

5.5. 约束

5.6. 系统列

5.7. 修改表

5.8. 权限

5.10. 模式

5.13. 外部数据

Chapter 6. 数据操纵

6.4. 从被修改的行中返回数据

MERGE INTO products p USING new_products n ON p.product_no = n.product_no
  WHEN NOT MATCHED THEN INSERT VALUES (n.product_no, n.name, n.price)
  WHEN MATCHED THEN UPDATE SET name = n.name, price = n.price
  RETURNING p.*;

Chapter 7. 查询

7.2. 表表达式

7.2.1.4 表函数
CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
    SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;

SELECT * FROM getfoo(1) AS t1;

CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);

function_call [AS] alias (column_definition [, ... ])
function_call AS [alias] (column_definition [, ... ])
ROWS FROM( ... function_call AS (column_definition [, ... ]) [, ... ] )
7.2.1.5. LATERAL子查询
SELECT o.order_id, o.customer, items.item_name, items.price
FROM orders o
CROSS JOIN LATERAL (
    SELECT item_name, price
    FROM order_items
    WHERE order_id = o.order_id  -- 这里引用了外层的 o.order_id
    ORDER BY price DESC
    LIMIT 2                      -- 只取前 2 个
) items;

7.7. VALUES 列表

Chapter 8. 数据类型

8.7. 枚举类型

8.11. 文本搜索类型

8.14. JSON 类型

8.14.5. jsonb 下标

8.15. 数组

8.16. 复合类型

--定义复合类型
CREATE TYPE complex AS (
    r       double precision,
    i       double precision
);

CREATE TYPE inventory_item AS (
    name            text,
    supplier_id     integer,
    price           numeric
);
--使用复合类型
CREATE TABLE on_hand (
    item      inventory_item,
    count     integer
);

INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);

--插入或更新整个列值
INSERT INTO mytab (complex_col) VALUES((1.1,2.2)); --省略了ROW
UPDATE mytab SET complex_col = ROW(1.1,2.2) WHERE ...; 
-- 更新组合列中的单个子字段，这里不需要（事实上也不能）给紧跟在SET后面的列名加圆括号，但在等号右侧的表达式中引用同一列时，则需要加圆括号。
UPDATE mytab SET complex_col.r = (complex_col).r + 1 WHERE ...;
--把子字段指定为INSERT的目标，如果没有为该列的所有子字段提供值，其余子字段就会填充为 NULL 值。
INSERT INTO mytab (complex_col.r, complex_col.i) VALUES(1.1, 2.2);
8.16.5. 在查询中使用复合类型

8.19. 对象标识符类型

8.20. pg_lsn 类型

8.21. 伪类型

Chapter 9. 函数和操作符

9.7. 模式匹配

9.8. 数据类型格式化函数

9.9. 时间/日期函数和操作符

9.13. 文本搜索函数和操作符

9.16. JSON 函数和操作符

9.19. 数组函数和操作符

9.27. 系统信息函数和操作符

9.28. 系统管理函数

Chapter 11. 索引

11.2. 索引类型

11.9. 仅索引扫描和覆盖索引

11.12. 检查索引使用情况

Chapter 12. 全文搜索

12.1. 介绍

12.2. 表和索引

12.3. 控制文本搜索

12.4. 附加特性

Chapter 14. 性能提示

14.2. 规划器使用的统计信息

Chapter 19. 服务器配置

19.1. 设置参数

关键参数

Chapter 20. 客户端认证

Chapter 21. 数据库角色

Chapter 22. 管理数据库

Chapter 24. 日常数据库维护任务

24.1. 日常清理

24.1.1. 清理基础

24.2. 日常重建索引

24.3. 日志文件维护

Chapter 25. 备份和恢复

25.1. SQL转储

25.2. 文件系统级备份

25.3. 持续归档和时间点恢复（PITR）

25.3.1. 设置 WAL 归档
25.3.2. 进行基础备份
25.3.3. 进行增量备份
25.3.4. 使用低级 API 进行基础备份
  1. 确保 WAL 归档已启用并正常工作。
  2. 以具有执行pg_backup_start权限的用户身份连接到服务器（连接哪个数据库并不重要）（超级用户，或者在该函数上被授予EXECUTE权限的用户），并发出以下命令：SELECT pg_backup_start(label => 'label', fast => false);
  3. 使用任何方便的文件系统备份工具执行备份，例如tar或cpio（不要使用pg_dump或pg_dumpall）。
  4. 在与之前相同的连接中，发出以下命令：SELECT * FROM pg_backup_stop(wait_for_archive => true);这会终止备份模式。
  5. 一旦备份期间活跃的 WAL 段文件都已归档，备份就完成了。
25.3.5. 使用持续归档备份进行恢复

Chapter 26. 高可用、负载均衡和复制

26.2. 日志传送备库

primary_conninfo = 'host=192.168.1.50 port=5432 user=foo password=foopass options=''-c wal_sender_timeout=5000'''
restore_command = 'cp /path/to/archive/%f %p'
archive_cleanup_command = 'pg_archivecleanup /path/to/archive %r'

Chapter 27. 监控数据库活动

27.2. 累计统计系统

27.3. 查看锁

Chapter 28. 可靠性与预写式日志

28.3. 预写式日志（WAL）

Chapter 29. 逻辑复制

29.1. 发布

29.1.1. 复制标识

29.2. 订阅

29.2.2. 示例：建立逻辑复制
--在发布端创建一些测试表。
/* pub # */ CREATE TABLE t1(a int, b text, PRIMARY KEY(a));
/* pub # */ CREATE TABLE t2(c int, d text, PRIMARY KEY(c));
/* pub # */ CREATE TABLE t3(e int, f text, PRIMARY KEY(e));
--在订阅端创建同样的表。
/* sub # */ CREATE TABLE t1(a int, b text, PRIMARY KEY(a));
/* sub # */ CREATE TABLE t2(c int, d text, PRIMARY KEY(c));
/* sub # */ CREATE TABLE t3(e int, f text, PRIMARY KEY(e));
--在发布端向表中插入数据。
/* pub # */ INSERT INTO t1 VALUES (1, 'one'), (2, 'two'), (3, 'three');
/* pub # */ INSERT INTO t2 VALUES (1, 'A'), (2, 'B'), (3, 'C');
/* pub # */ INSERT INTO t3 VALUES (1, 'i'), (2, 'ii'), (3, 'iii');
--为这些表创建发布。发布 pub2 和 pub3a 禁用了部分 publish 操作。发布 pub3b 使用了行过滤器（见 Section 29.4）。
/* pub # */ CREATE PUBLICATION pub1 FOR TABLE t1;
/* pub # */ CREATE PUBLICATION pub2 FOR TABLE t2 WITH (publish = 'truncate');
/* pub # */ CREATE PUBLICATION pub3a FOR TABLE t3 WITH (publish = 'truncate');
/* pub # */ CREATE PUBLICATION pub3b FOR TABLE t3 WHERE (e > 5);
--为这些发布创建订阅。订阅 sub3 同时订阅 pub3a 和 pub3b。默认情况下所有订阅都会复制 初始数据。
/* sub # */ CREATE SUBSCRIPTION sub1
/* sub - */ CONNECTION 'host=localhost dbname=test_pub application_name=sub1'
/* sub - */ PUBLICATION pub1;
/* sub # */ CREATE SUBSCRIPTION sub2
/* sub - */ CONNECTION 'host=localhost dbname=test_pub application_name=sub2'
/* sub - */ PUBLICATION pub2;
/* sub # */ CREATE SUBSCRIPTION sub3
/* sub - */ CONNECTION 'host=localhost dbname=test_pub application_name=sub3'
/* sub - */ PUBLICATION pub3a, pub3b;
--注意，无论发布的 publish 操作如何，初始表数据都会被复制。（不受行过滤器影响）
--在常规复制阶段会使用相应的 publish 操作。这意味着发布 pub2 与 pub3a 不会复制 INSERT。

29.3. 逻辑复制故障切换

29.4. 行过滤器

29.5. 列列表

29.12. 配置参数

29.12.1. 发布端
29.12.2. 订阅端

Chapter 36. 扩展 SQL

36.5. 查询语言（SQL）函数

CREATE TABLE emp (
    name        text,
    salary      numeric,
    age         integer,
    cubicle     point
);
-- 返回复合类型emp（列表顺序必须与列在复合类型中出现的顺序完全相同；类型必须匹配）
CREATE FUNCTION new_emp() RETURNS emp AS $$
    SELECT text 'None' AS name,
        1000.0 AS salary,
        25 AS age,
        point '(2,2)' AS cubicle;
$$ LANGUAGE SQL;

-- 返回多列（本质上该函数的结果创建了一个匿名复合类型）
-- 在从 SQL 调用这样一个函数时，输出参数不会被包括在调用参数列表中。
CREATE FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int)
AS 'SELECT x + y, x * y'
LANGUAGE SQL;
-- 等价效果
CREATE TYPE sum_prod AS (sum int, product int);
CREATE FUNCTION sum_n_product (int, int) RETURNS sum_prod
AS 'SELECT $1 + $2, $1 * $2'
LANGUAGE SQL;

-- 可变参数：只要所有 “可选”参数都属于同一种数据类型。可选参数会以数组形式传递给函数。定义这类函数时，需要把最后一个参数标记为 VARIADIC；该参数必须被声明为数组类型
CREATE FUNCTION mleast(VARIADIC arr numeric[]) RETURNS numeric AS $$
    SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i);
$$ LANGUAGE SQL;
-- 调用方式
SELECT mleast(10, -1, 5, 4.4);
SELECT mleast(ARRAY[10, -1, 5, 4.4]);    -- doesn't work
SELECT mleast(VARIADIC ARRAY[10, -1, 5, 4.4]);
SELECT mleast(VARIADIC ARRAY[]::numeric[]); --在调用中指定VARIADIC也是向 variadic 函数传递空数组的唯一方式
SELECT mleast(); -- 不会匹配mleast(VARIADIC arr numeric[])，除非定义一个无参的同名函数

-- 带默认值
CREATE FUNCTION foo(a int, b int DEFAULT 2, c int = 3)
RETURNS int
LANGUAGE SQL
AS $$
    SELECT $1 + $2 + $3;
$$;

-- 返回集合的SQL函数
CREATE TABLE foo (fooid int, foosubid int, fooname text);
INSERT INTO foo VALUES (1, 1, 'Joe');
INSERT INTO foo VALUES (1, 2, 'Ed');
INSERT INTO foo VALUES (2, 1, 'Mary');
CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
    SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1;

-- 通过输出参数定义的列来返回多行
-- 必须写成 RETURNS SETOF record，以表明该函数返回的是多行而不是单行。
-- 如果只有一个输出参数，则写该参数的类型，而不是 record
CREATE TABLE tab (y int, z int);
INSERT INTO tab VALUES (1, 2), (3, 4), (5, 6), (7, 8);
CREATE FUNCTION sum_n_product_with_tab (x int, OUT sum int, OUT product int)
RETURNS SETOF record
AS $$
    SELECT $1 + tab.y, $1 * tab.y FROM tab;
$$ LANGUAGE SQL;

--通过多次调用集合返回函数来构造查询结果通常很有用，其中每次调用的参数都来自表或子查询的连续行。
SELECT * FROM nodes;
   name    | parent
-----------+--------
 Top       |
 Child1    | Top
 Child2    | Top
 Child3    | Top
 SubChild1 | Child1
 SubChild2 | Child1
-- 获取text的子节点
CREATE FUNCTION listchildren(text) RETURNS SETOF text AS $$
    SELECT name FROM nodes WHERE parent = $1
$$ LANGUAGE SQL STABLE;
-- 对于nodes中的每个name，调用listchildren，并把结果join在一起
SELECT name, child FROM nodes, LATERAL listchildren(name) AS child;
-- 等价写法
SELECT name, listchildren(name) FROM nodes;
  name  |   child
--------+-----------
 Top    | Child1
 Top    | Child2
 Top    | Child3
 Child1 | SubChild1
 Child1 | SubChild2

-- 返回TABLE的SQL函数：不允许把显式的OUT或者INOUT参数用于 RETURNS TABLE 记法 — 必须把所有输出列放在 TABLE 列表中
CREATE FUNCTION sum_n_product_with_tab (x int)
RETURNS TABLE(sum int, product int) AS $$
    SELECT $1 + tab.y, $1 * tab.y FROM tab;
$$ LANGUAGE SQL;

-- 多态SQL函数
CREATE FUNCTION make_array(anyelement, anyelement) RETURNS anyarray AS $$
    SELECT ARRAY[$1, $2];
$$ LANGUAGE SQL;

-- 指定比较规则
CREATE FUNCTION anyleast (VARIADIC anyarray) RETURNS anyelement AS $$
    SELECT min($1[i] COLLATE "en_US") FROM generate_subscripts($1, 1) g(i);
$$ LANGUAGE SQL;

36.6. 函数重载

36.7. 函数易变性分类

36.17. 将相关对象打包成扩展

Chapter 37. 触发器

37.2. 数据更改的可见性

Chapter 38. 事件触发器

Chapter 41. PL/pgSQL — SQL 过程语言

CREATE FUNCTION somefunc(integer, text) RETURNS integer
AS 'function body text'
LANGUAGE plpgsql;
-- function body text
[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements
END [ label ];

41.3. 声明

41.4. 表达式

41.5. 基本语句

41.5.2. 执行 SQL 命令
41.5.3. 执行返回单行结果的命令
BEGIN
    SELECT * INTO STRICT myrec FROM emp WHERE empname = myname;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RAISE EXCEPTION 'employee % not found', myname;
        WHEN TOO_MANY_ROWS THEN
            RAISE EXCEPTION 'employee % not unique', myname;
END;

CREATE FUNCTION get_userid(username text) RETURNS int
AS $$
#print_strict_params on
DECLARE
userid int;
BEGIN
    SELECT users.userid INTO STRICT userid
        FROM users WHERE users.username = get_userid.username;
    RETURN userid;
END;
$$ LANGUAGE plpgsql;

ERROR:  query returned no rows
DETAIL:  parameters: username = 'nosuchuser'
CONTEXT:  PL/pgSQL function get_userid(text) line 6 at SQL statement
41.5.4. 执行动态命令
41.5.5. 获取结果状态

41.6. 控制结构

41.6.1. 从函数返回
RETURN NEXT expression; -- 可用于标量和组合数据类型；对于组合结果类型，会返回完整的结果“表”
RETURN QUERY query;     -- 会把查询执行结果追加到函数的结果集中 
RETURN QUERY EXECUTE command-string [ USING expression [, ... ] ];
41.6.2. 从过程返回
41.6.3. 调用过程
41.6.4. 条件语句
IF ... THEN ... END IF
IF ... THEN ... ELSE ... END IF
IF ... THEN ... ELSIF ... THEN ... ELSE ... END IF --关键词ELSIF也可以被拼写成ELSEIF。

-- 简单式 CASE, search-expression被计算一次，找匹配的结果，如果没有找到匹配，则执行 ELSE statements；但如果没有 ELSE，则会抛出 CASE_NOT_FOUND 异常。
CASE search-expression
    WHEN expression [, expression [ ... ]] THEN
      statements
  [ WHEN expression [, expression [ ... ]] THEN
      statements
    ... ]
  [ ELSE
      statements ]
END CASE;

-- 搜索式 CASE，每个布尔表达式结算一遍，如果没有找到为真的结果，就执行ELSE statements；但如果不存在ELSE，则会抛出CASE_NOT_FOUND异常。
CASE
    WHEN boolean-expression THEN
      statements
  [ WHEN boolean-expression THEN
      statements
    ... ]
  [ ELSE
      statements ]
END CASE;
41.6.5. 简单循环
41.6.5.1. LOOP
[ <<label>> ]
LOOP
    statements
END LOOP [ label ];
41.6.5.2. EXIT
<<ablock>>
BEGIN
    -- 一些计算
    IF stocks > 100000 THEN
        EXIT ablock;  -- 导致从 BEGIN 块中退出
    END IF;
    -- 当stocks > 100000时，这里的计算将被跳过
END;
41.6.5.3. CONTINUE
41.6.5.4. WHILE
[ <<label>> ]
WHILE boolean-expression LOOP
    statements
END LOOP [ label ];
41.6.5.5. FOR（整型变体）
[ <<label>> ]
FOR name IN [ REVERSE ] expression .. expression [ BY expression ] LOOP
    statements
END LOOP [ label ];
41.6.6. 遍历查询结果
[ <<label>> ]
FOR target IN query LOOP
    statements
END LOOP [ label ];

[ <<label>> ]
FOR target IN EXECUTE text_expression [ USING expression [, ... ] ] LOOP
    statements
END LOOP [ label ];
41.6.7. 遍历数组
[ <<label>> ]
FOREACH target [ SLICE number ] IN ARRAY expression LOOP
    statements
END LOOP [ label ];

CREATE FUNCTION scan_rows(int[]) RETURNS void AS $$
DECLARE
  x int[];
BEGIN
  FOREACH x SLICE 1 IN ARRAY $1
  LOOP
    RAISE NOTICE 'row = %', x;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

SELECT scan_rows(ARRAY[[1,2,3],[4,5,6],[7,8,9],[10,11,12]]);

NOTICE:  row = {1,2,3}
NOTICE:  row = {4,5,6}
NOTICE:  row = {7,8,9}
NOTICE:  row = {10,11,12}
41.6.8. 捕获错误
[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements
EXCEPTION
    WHEN condition [ OR condition ... ] THEN
        handler_statements
    [ WHEN condition [ OR condition ... ] THEN
          handler_statements
      ... ]
END;

WHEN division_by_zero THEN ...
WHEN SQLSTATE '22012' THEN ...
41.6.8.1. 获取错误信息
错误诊断项名称类型描述
RETURNED_SQLSTATEtext该异常的 SQLSTATE 错误代码
COLUMN_NAMEtext与异常相关的列名
CONSTRAINT_NAMEtext与异常相关的约束名
PG_DATATYPE_NAMEtext与异常相关的数据类型名
MESSAGE_TEXTtext该异常的主要消息的文本
TABLE_NAMEtext与异常相关的表名
SCHEMA_NAMEtext与异常相关的模式名
PG_EXCEPTION_DETAILtext该异常的详细消息文本（如果有）
PG_EXCEPTION_HINTtext该异常的提示消息文本（如果有）
PG_EXCEPTION_CONTEXTtext描述产生异常时调用栈的文本行
41.6.9. 获得执行位置信息

41.7. 游标

41.7.1. 声明游标变量
DECLARE
    curs1 refcursor;
    curs2 CURSOR FOR SELECT * FROM tenk1;
    curs3 CURSOR (key integer) FOR SELECT * FROM tenk1 WHERE unique1 = key;
41.7.2. 打开游标
41.7.2.1. OPEN FOR query
41.7.2.2. OPEN FOR EXECUTE
41.7.2.3. 打开已绑定游标
OPEN curs2;
OPEN curs3(42);
OPEN curs3(key := 42);
OPEN curs3(key => 42);

DECLARE
    key integer;
    curs4 CURSOR FOR SELECT * FROM tenk1 WHERE unique1 = key;
BEGIN
    key := 42;
    OPEN curs4;
41.7.3. 使用游标
41.7.3.1. FETCH
FETCH curs1 INTO rowvar;
FETCH curs2 INTO foo, bar, baz;
FETCH LAST FROM curs3 INTO x, y;
FETCH RELATIVE -2 FROM curs4 INTO x;
41.7.3.2. MOVE
MOVE curs1;
MOVE LAST FROM curs3;
MOVE RELATIVE -2 FROM curs4;
MOVE FORWARD 2 FROM curs4;
41.7.3.3. UPDATE/DELETE WHERE CURRENT OF
41.7.3.4. CLOSE
41.7.3.5. 返回游标
-- 调用者提供游标名字的方法
CREATE TABLE test (col text);
INSERT INTO test VALUES ('123');

CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '
BEGIN
    OPEN $1 FOR SELECT col FROM test;
    RETURN $1;
END;
' LANGUAGE plpgsql;

BEGIN;
SELECT reffunc('funccursor');
FETCH ALL IN funccursor;
COMMIT;
41.7.4. 遍历游标结果
[ <<label>> ]
FOR recordvar IN bound_cursorvar [ ( [ argument_name { := | => } ] argument_value [, ...] ) ] LOOP
    statements
END LOOP [ label ];

41.8. 事务管理

CREATE PROCEDURE transaction_test1()
LANGUAGE plpgsql
AS $$
BEGIN
    FOR i IN 0..9 LOOP
        INSERT INTO test1 (a) VALUES (i);
        IF i % 2 = 0 THEN
            COMMIT;
        ELSE
            ROLLBACK;
        END IF;
    END LOOP;
END;
$$;

CALL transaction_test1();

CREATE PROCEDURE transaction_test2()
LANGUAGE plpgsql
AS $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN SELECT * FROM test2 ORDER BY x LOOP
        INSERT INTO test1 (a) VALUES (r.x);
        COMMIT;
    END LOOP;
END;
$$;

CALL transaction_test2();

41.9. 错误和消息

RAISE [ level ] 'format' [, expression [, ... ]] [ USING option { = | := } expression [, ... ] ];
RAISE [ level ] condition_name [ USING option { = | := } expression [, ... ] ];
RAISE [ level ] SQLSTATE 'sqlstate' [ USING option { = | := } expression [, ... ] ];
RAISE [ level ] USING option { = | := } expression [, ... ];
RAISE ;

RAISE NOTICE 'Calling cs_create_job(%)', v_job_id;    -- format
RAISE division_by_zero;                               -- condition_name
RAISE WARNING SQLSTATE '22012';                       -- SQLSTATE

-- 用给定的错误消息和提示中止事务：
RAISE EXCEPTION 'Nonexistent ID --> %', user_id USING HINT = 'Please check your user ID';
      
-- 这两个示例展示了设置 SQLSTATE 的两种等价的方法：
RAISE 'Duplicate user ID: %', user_id USING ERRCODE = 'unique_violation';
RAISE 'Duplicate user ID: %', user_id USING ERRCODE = '23505';

-- 另一种得到前面示例相同结果的方式是：
RAISE unique_violation USING MESSAGE = 'Duplicate user ID: ' || user_id;
41.9.2. 检查断言

41.10. 触发器函数

41.10.1. 数据更改触发器
41.10.2. 事件触发器

41.11. PL/pgSQL 内部机制

41.11.1. 变量替换
#variable_conflict error
#variable_conflict use_variable
#variable_conflict use_column

--这些命令只影响它们所属的函数，并且会覆盖plpgsql.variable_conflict的设置。一个示例是：
CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $$
    #variable_conflict use_variable
    DECLARE
        curtime timestamp := now();
    BEGIN
        UPDATE users SET last_modified = curtime, comment = comment
          WHERE users.id = id;
    END;
$$ LANGUAGE plpgsql;