变量使用实例 CALL pcollatepinyin(); NOTICE: 严 > 丰 CALL teledb 变量赋值 plaintext teledb CREATE OR REPLACE PROCEDURE psetval() AS $$ DECLARE 定义时赋值 vint1 integer 1; 使用 :兼容于plsql vint2 integer : 1; vtxt1 text; vfloat float8; 使用查询赋值 vrelname text (select relname FROM pgclass LIMIT 1); vrelpages integer; vrec RECORD; BEGIN 在函数体中赋值 vtxt1 'teledbpg'; vfloat random(); 使用查询赋值的另一种方式 SELECT relname,relpages INTO vrelname,vrelpages FROM pgclass ORDER BY random() LIMIT 1; RAISE NOTICE 'vrelname % , relpages %',vrelname,vrelpages; END; $$ LANGUAGE plpgsql; CREATE PROCEDURE teledb CALL psetval(); NOTICE: vrelname pgtoast17220index , relpages 1 CALL teledb BULK COLLECT BULK COLLECT 将一个查询结果集保存起来。 示例1 plaintext teledb create table t5(f1 integer,f2 varchar(10)); CREATE TABLE teledb insert into t5 values(1,'teledbpg1'); INSERT 0 1 teledb insert into t5 values(2,'teledbpg2'); INSERT 0 1 teledb create or replace procedure pbulkcollect() AS $$ declare TYPE t5list IS TABLE OF t5.f2%TYPE; t5s t5list; BEGIN SELECT f2 BULK COLLECT INTO t5s FROM t5; FOR i IN t5s.FIRST .. t5s.LAST LOOP raise notice '%',t5s[i]; END LOOP; END $$language plpgsql; NOTICE: type reference t5.f2%TYPE converted to character varying CREATE PROCEDURE teledb CALL pbulkcollect(); NOTICE: teledbpg1 NOTICE: teledbpg2 CALL 示例2 plaintext teledb