CREATE OR REPLACE FUNCTION fun_affect_rows() RETURNS text AS $BODY$ declare v_count int;begin insert into test values(99),(98); GET DIAGNOSTICS v_count = ROW_COUNT; raise notice '本次插入数据量 %', v_count; delete from test where id < 15; GET DIAGNOSTICS v_count = ROW_COUNT; ra...
GET DIAGNOSTICS v_column = ROW_COUNT; v_endtime:=to_char(now(),'yyyy/mm/dd hh24:mi:ss'); insert into T_LOG(pro_name,tj_date,begin_time,end_time,err_infor,run_result,v_sql,sql_count) values(pro_name,p_date,v_begintime,v_endtime,v_sqlerrm,'Y',v_sql,v_column); -- 统计...
tg02 before insert on digoal for each row execute procedure debug(); CREATE TRIGGER postgres=# create trigger tg03 after insert on digoal for each row execute procedure debug(); CREATE TRIGGER postgres=# create trigger tg04 after insert on digoal for each statement execute procedure debug(); ...
BEGIN LOOP UPDATE your_table SET column1 = new_value1, column2 = new_value2 FROM ( SELECT id, new_value1, new_value2 FROM your_update_source ORDER BY id LIMIT batch_size OFFSET offset ) AS t WHERE your_table.id = t.id; GET DIAGNOSTICS offset = ROW_COUNT; EXIT WHEN offset = 0...
GET DIAGNOSTICS variable = item [ , ... ] ; 这条命令允许我们检索系统状态标识符。每个 item 是一个关键字,表示一个将要赋予该特定变量的状态值(该变量应该和要接收的数值类型相同)。 当前可用的状态项有 ROW_COUNT, 最后一个发送给 SQL 引擎的 SQL 命令处理的行的数量,和 RESULT_OID,最后一条 SQL 命...
GET DIAGNOSTICS捕获运行过程中的状态值 There are several ways to determine the effect of a command. The first method is to use the GET DIAGNOSTICS command, which has the form: GET [ CURRENT ] DIAGNOSTICS variable { = | := } item [ , ... ]; GET DIAGNOSTICS integer_var = ROW_COUNT;...
PL/pgSQL提供了两种方法来检查一个命令的执行结果。第一种方法是使用GET DIAGNOSTICS命令。它的形式如下: GET DIAGNOSTICS variable = item [ , ... ]; 1. 其中的item可以是ROW_COUNT和RESULT_OID。ROW_COUNT表示上一条SQL命令处理的数据行的个数。RESULT_OID表示最近的SQL命令插入的最后一个数据行的OID。varia...
对于merge into语法的转换,在ADB for PG中先使用update进行更新,然后使用GET DIAGNOSTICS rowcount := ROW_COUNT;语句获取update更新的行数,如果update更新的行数为0,那么再使用insert语句进行插入。 MERGE INTO test1 t1 USING (SELECT t2.col1 col1, t3.col2 col2, ...
('pg_class'::regclass, tbl, colnum) AND classid = 'pg_class'::regclass AND objsubid = 0 AND deptype = 'a'; GET DIAGNOSTICS count = ROW_COUNT; IF count < 1 THEN RAISE EXCEPTION 'no linked sequence found'; ELSIF count > 1 THEN RAISE EXCEPTION 'more than one linked sequence ...
postgres$# GET DIAGNOSTICS v_row_count = ROW_COUNT; postgres$# RAISE NOTICE '查询到的记录数为 % ',v_row_count; postgres$# END; postgres$# $$ postgres-# LANGUAGE plpgsql; CREATE FUNCTION postgres=# SELECT f27(1); NOTICE: 查询到的记录数为 1 f27 --- (1 row) postgres=# SELECT...