PostgreSQL命令速查 – 事务与过程语言

官方文档参考这里,Exception码参考这里

事务

在PostgreSQL里,一组被BEGIN和COMMIT包围的语句称为一个事务块,没有显式发起BEGIN命令的语句默认作为单独一个事务执行。事务过程中失败将回滚。

BEGIN;                    -- 开始一个事务块
    UPDATE user_info SET information = 'new info2', sex = DEFAULT, age = age + 1 WHERE name = '张三';
    INSERT INTO user_message_201701 VALUES ('f4daf1ef-67a7-4887-b5e2-6e13a1d9197d', 2, 1, 'be_friend', NULL, '2017-01-15 16:11:26', 0);
COMMIT;                   -- 提交事务

BEGIN;
    UPDATE user_info SET information = 'new info2', sex = DEFAULT, age = age + 1 WHERE name = '张三';
SAVEPOINT my_savepoint;              -- 设置还原点
    INSERT INTO user_message_201701 VALUES ('f4daf1ef-67a7-4887-b5e2-6e13a1d9197d', 2, 1, 'be_friend', NULL, '2017-01-15 16:11:26', 0);
    DELETE FROM user_message WHERE from_user_id <> 1;
ROLLBACK TO my_savepoint;            -- 事务失败时回滚,可以指定回滚到前面的某个还原点
    UPDATE user_message SET msg_item = ROW(2,3,150) FROM user_info WHERE user_info.id = user_message.to_user_id;
COMMIT;

PL/pgSQL

PostgreSQL既支持直接执行一组plpgsql语句,又支持将语句写在function中通过函数名反复调用执行。一组过程语句被BEGIN和END包围,内部不形成事务,通过EXCEPTION块来处理异常情况。

直接执行一组plpgsql语句

DO LANGUAGE plpgsql $$ 
DECLARE
    num numeric;
BEGIN
    num := random();
    CASE
        WHEN num BETWEEN 0.1 AND 0.5 THEN
            RAISE NOTICE 'num : %', num;
        WHEN num BETWEEN 0.5 AND 0.9 THEN
            RAISE NOTICE 'num : %', num;
    END CASE;
    RAISE NOTICE 'Print current time. timeofday(), current_timestamp, now(), statement_timestamp(), transaction_timestamp(), clock_timestamp()';
    FOR i IN 1.. 20 
    LOOP
        RAISE NOTICE '%, %, %, %, %, %', timeofday(), current_timestamp, now(), statement_timestamp(), transaction_timestamp(), clock_timestamp();
    END LOOP; 
END $$;

创建自定义函数,存储过程

DROP FUNCTION func_a();

CREATE FUNCTION func_a() RETURNS VOID AS $$                  -- RETURNS VOID 无返回值
BEGIN
    RAISE NOTICE 'Hello func_a()';                           -- RAISE NOTICE 打印消息
END;
$$ LANGUAGE plpgsql;
DROP FUNCTION func_a(varchar, integer[]);

CREATE OR REPLACE FUNCTION func_a(url varchar, nums integer[]) RETURNS text AS $$        -- 定义参数名称及类型,返回text类型值
<< outerblock >>                                             -- 定义块标签
DECLARE
    distance    numeric(5,2);
    begin_tms   timestamp DEFAULT clock_timestamp();         -- DEFAULT 进入方法体时赋值
    index       integer := 1;                                -- := 预编译时赋值
    key         CONSTANT varchar := 'static string';         -- CONSTANT 常量
    result      text;
BEGIN
    RAISE NOTICE 'Begin time is %', begin_tms;
    -- 创建一个子过程
    DECLARE
        begin_tms timestamp DEFAULT clock_timestamp();
    BEGIN
        RAISE NOTICE 'Begin time in sub block is %, Outer begin time is %', begin_tms, outerblock.begin_tms;   -- 子块调用外层块的变量
    END;
    RETURN result;
END;
$$ LANGUAGE plpgsql;
DROP FUNCTION func_a(text);

CREATE OR REPLACE FUNCTION func_a(text, OUT integer, OUT integer) AS $$       -- 定义输入和输出变量的类型
DECLARE
    ui_row               user_info%ROWTYPE;                    -- 声明变量,类型是user_info表的行类型
    ui_col_information   user_info.information%TYPE;           -- 声明变量,类型是user_info表的information字段的类型
    def_record           RECORD;                               -- 声明变量,类型是自定义的行类型
    ip_1                 ALIAS FOR $1;                         -- 将函数第一个变量$1命名为i_param 
    op_rs_1              ALIAS FOR $2;
    op_rs_2              ALIAS FOR $3;
    row_count bigint;
BEGIN
    PERFORM func_a();                                                                       -- 执行没有返回结果的查询或者函数需要用 PERFORM 

    SELECT * INTO ui_row FROM user_info WHERE age >= (SELECT avg(age) FROM user_info) ORDER BY id;        -- 将查询结果第一条赋值给变量ui_row
    RAISE NOTICE 'ui_row : %', ui_row;

    BEGIN                                                                                   -- 为了当下目标语句抛出异常后仍然可以继续执行后面的语句,新开个语句块处理异常
        def_record := row(NULL);                                                            -- 为了后面使用 def_record 时不报 record "def_record" is not assigned yet 异常
        SELECT * INTO STRICT def_record FROM user_info WHERE name like '%' || ip_1 || '%';      -- STRICT 查询结果必须唯一,否则报错
    EXCEPTION                                                     -- 捕获异常
        WHEN NO_DATA_FOUND THEN
            RAISE NOTICE 'record like % not found', ip_1;
        WHEN TOO_MANY_ROWS THEN
            RAISE NOTICE 'record like % not unique.', ip_1;
        WHEN OTHERS THEN
            RAISE EXCEPTION 'record like % exception', ip_1;      -- 如果 RAISE EXCEPTION 则继续抛出异常到上一层方法体
    END;
    RAISE NOTICE 'def_record : %', def_record;

    ui_col_information := (SELECT information FROM user_info WHERE name = ip_1 ORDER BY id);      -- 将查询或函数返回结果赋值给相应类型变量
    IF NOT FOUND                     -- 特殊布尔型变量 FOUND 判断上一条语句的状态
    THEN
        RAISE NOTICE 'information of % not found', ip_1;
    ELSE
        RAISE NOTICE 'ui_col_information : %', ui_col_information;
    END IF;

    UPDATE user_info SET information = 'new info4' WHERE name like '%' || ip_1 || '%';
    GET DIAGNOSTICS row_count = ROW_COUNT;                                                  -- 特殊bigint型变量 ROW_COUNT 得到上一条语句结果行数
    RAISE NOTICE 'row_count : %', row_count;

    op_rs_2 := ui_row.age;                      -- 如果放在op_rs_1后面,则当op_rs_1抛异常时不能执行
    op_rs_1 := (1 + 2 - 3) * 4 / row_count;
EXCEPTION
    WHEN division_by_zero THEN
        NULL;                                -- 什么也不做,忽略异常
END;
$$ LANGUAGE plpgsql;

SELECT * FROM func_a('张三');
SELECT * FROM func_a('ran_1');
SELECT * FROM func_a('ran_00000');
DROP FUNCTION func_a(user_info);

CREATE OR REPLACE FUNCTION func_b(ui_row user_info) RETURNS TABLE (num integer, total numeric, info text) AS $BODY$
DECLARE
    ui_count   bigint;
    ui_name    varchar;
BEGIN
    IF ui_row.age < 15 OR ui_row.age > 35 THEN
        EXECUTE 'UPDATE user_info SET ' || quote_ident('information') || ' = ' || quote_nullable('abcde') || ' WHERE id = ' || quote_nullable(ui_row.id);   --EXECUTE 把字符串当做SQL语句执行
        RETURN QUERY SELECT count(id)::integer, sum(height), '->15 35<-'::text FROM user_info WHERE age >= 15 AND age <= 25;
    ELSIF ui_row.age >= 15 AND ui_row.age <= 25 THEN
        EXECUTE format('UPDATE user_info SET %I = $1 WHERE id = $2', 'information') USING 'ABCDE', ui_row.id;                                 --EXECUTE ... USING... 变量替换
        RETURN QUERY EXECUTE 'SELECT count(id)::integer, sum(height), ''15<->25''::text FROM user_info WHERE age >= 15 AND age <= 25';
    ELSE
        NULL;
    END IF;
    RAISE NOTICE 'function is not finished';
    EXECUTE 'SELECT count(*) FROM user_info WHERE name like $1' INTO ui_count USING '%ran_%';        --EXECUTE ... INTO ... USING... 
    RAISE NOTICE 'ui_count : %', ui_count;
    UPDATE user_info SET reg_position = ui_row.reg_position WHERE id > ui_row.id RETURNING name INTO ui_name;    --UPDATE ... RETURNING ... INTO 仅将第一条数据赋值给变量
END;
$BODY$ LANGUAGE plpgsql;

SELECT func_b(ui.*) FROM user_info ui where ui.age < 25 limit 1;     --找到符合条件的行,并使函数应用于这一行
DROP FUNCTION func_b(anyarray, anyelement, anynonarray, anyrange);

CREATE OR REPLACE FUNCTION func_b(v1 anyarray, v2 anyelement, v3 anynonarray, v4 anyrange) RETURNS anyelement AS $$
DECLARE
    result      ALIAS FOR $0;
    num         integer := 0;
    x           integer;
    y           integer[];
BEGIN
    <<ablock>>
    WHILE num < 100 LOOP
        num := num + 1;
        CONTINUE WHEN num < 10;
        EXIT WHEN num > 30;
        IF num > 20 THEN
            EXIT ablock;                               -- 退出到指定标签
        END IF;
        RAISE NOTICE 'num : %', num;
    END LOOP;

    FOR i IN REVERSE 10..1 BY 2 LOOP                   -- 从10到1遍历,步差2
        RAISE NOTICE 'reverse 10 to 1 by 2 step : %', i;
    END LOOP;

    FOREACH x IN ARRAY $1                              -- 遍历数组
    LOOP
        RAISE NOTICE 'row = %', x;
    END LOOP;

    FOREACH y SLICE 1 IN ARRAY v1                      -- SLICE 1 按数组维度遍历元素
    LOOP
        RAISE NOTICE 'row slice 2 = %', y;
    END LOOP;

    result := v2;
    RETURN result;
END;
$$ LANGUAGE plpgsql;

SELECT func_b(ARRAY[[1,2,3],[4,5,6],[7,8,9],[10,11,12]], NULL, NULL, '[1, 9]'::int4range);
CREATE OR REPLACE FUNCTION func_c() RETURNS SETOF character varying AS $$      -- 返回单列多行
DECLARE
    rs_item   character varying;
    curs1     refcursor;
    curs2     CURSOR FOR SELECT * FROM user_info;
    curs3     CURSOR (key varchar) FOR SELECT * FROM user_info WHERE name like key;
    ui_row    user_info%ROWTYPE;
BEGIN
    FOR rs_item IN                                      -- 逐行处理查询结果集
        SELECT name FROM user_info WHERE age < 20 OR age > 30 ORDER BY id
    LOOP
        RETURN NEXT rs_item;                            -- 将当前行加入到返回结果集
    END LOOP;

    OPEN curs1 FOR EXECUTE format('SELECT * FROM %I WHERE jsonb_data = $1', 'user_info') USING '{}'::jsonb;          -- 打开cursor
    FETCH curs1 INTO ui_row;                            -- 移动cursor并取值
    RAISE NOTICE 'curs1: %', ui_row;
    FETCH LAST FROM curs1 INTO ui_row;
    RAISE NOTICE 'curs1: %', ui_row;
    FETCH RELATIVE -2 FROM curs1 INTO ui_row;
    RAISE NOTICE 'curs1: %', ui_row;
    CLOSE curs1;
    
    --OPEN curs2;
    FOR ui_row IN curs2                                 -- 遍历cursor 被遍历的cursor必须是事先绑定查询对象的且未打开
    LOOP
        RETURN NEXT 'curs2: ' || ui_row.name;           -- 将当前行加入到返回结果集
    END LOOP;
    --CLOSE curs2;

    OPEN curs3(key := '%ran%');
    MOVE curs3;                                         -- 仅移动cursor
    MOVE FORWARD 2 FROM curs3;
    UPDATE user_info SET information = 'cur3 info' WHERE CURRENT OF curs3;        -- 更新当前cursor位置的数据
    CLOSE curs3;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM func_c();
CREATE OR REPLACE FUNCTION func_d() RETURNS SETOF record AS $$           -- 返回多列多行
DECLARE
    ui_row     record;
BEGIN
    FOR ui_row IN                                      -- 逐行处理查询结果集
        SELECT age, height, information FROM user_info WHERE age < 20 OR age > 30 ORDER BY id
    LOOP
        RETURN NEXT ui_row;                            -- 将当前行加入到返回结果集
    END LOOP;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM func_d() AS tbl01 (a int2, h numeric, i text);
CREATE FUNCTION func_e() RETURNS refcursor AS $$       -- 返回cursor
DECLARE
    ref refcursor;
BEGIN
    OPEN ref FOR SELECT * FROM user_info;
    RETURN ref;
END;
$$ LANGUAGE plpgsql;

-- 使用cursor需要在事务中
BEGIN;
SELECT func_e();    -- 输出cursor名字 <unnamed portal 1>
FETCH ALL IN "<unnamed portal 1>";
COMMIT;
Creative Commons License

本文基于署名-非商业性使用-相同方式共享 4.0许可协议发布,欢迎转载、使用、重新发布,但请保留文章署名wanghengbin(包含链接:https://wanghengbin.com),不得用于商业目的,基于本文修改后的作品请以相同的许可发布。

发表评论