变量声明语法

name [ CONSTANT ] type [ COLLATE collation_name ] [ NOT NULL ] [ { DEFAULT | := | = } expression ];

如果给定DEFAULT子句，它会指定进入该块时分配给该变量的初始值。如果没有给出 DEFAULT子句，则该变量被初始化为SQL空值。

CONSTANT选项阻止该变量在初始化之后被赋值，这样它的值在块的持续期内保持不变。

COLLATE选项指定用于该变量的一个排序规则。如果指定了NOT NULL，对该变量赋值为空值会导致一个运行时错误。所有被声明为NOT NULL的变量必须被指定一个非空默认值。等号（=）可以被用来代替 PL/SQL-兼容的:=。

定义一个普通变量

teledb=# CREATE OR REPLACE PROCEDURE ordinary_var() AS
$$
DECLARE
    --所有变量的声明都要放在这里,建议变量以v_开头,参数以a_开头
    v_int integer := 1;
    v_text text;  
BEGIN
    v_text = 'teledb_pg';
    RAISE NOTICE 'v_int = %',v_int;  
    RAISE NOTICE 'v_text = %',v_text;  
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
teledb=# CALL ordinary_var();                       
NOTICE:  v_int = 1
NOTICE:  v_text = teledb_pg
CALL
teledb=# 

定义CONSTANT 变量

teledb=# CREATE OR REPLACE PROCEDURE p_constant()  AS
$$
DECLARE  
    v_int CONSTANT integer := 1;   
BEGIN
    RAISE NOTICE 'v_int = %',v_int;  
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
teledb=# CALL p_constant();
NOTICE:  v_int = 1
CALL

CONSTANT 不能再次赋值

teledb=# CREATE OR REPLACE PROCEDURE p_constant()  AS
$$
DECLARE  
    v_int CONSTANT integer := 1;   
BEGIN
    RAISE NOTICE 'v_int = %',v_int;  
v_int = 10;
END;
$$
LANGUAGE plpgsql;
ERROR:  "v_int" is declared CONSTANT
LINE 7: v_int = 10;
        ^
teledb=#

定义NOT NULL变量

teledb=# CREATE OR REPLACE PROCEDURE p_not_null_var() AS
$$
DECLARE  
    v_int integer NOT NULL := 1;   
BEGIN
    RAISE NOTICE 'v_int = %',v_int;  
    SELECT NULL INTO v_int;
    RAISE NOTICE 'v_int = %',v_int;  
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
teledb=# CALL p_not_null_var();
NOTICE:  v_int = 1
ERROR:  null value cannot be assigned to variable "v_int" declared NOT NULL
CONTEXT:  PL/pgSQL function p_not_null_var() line 6 at SQL statement
teledb=#  

定义为NOT NULL变量，则该变量受NOT NULL约束

定义COLLATE 变量

按unicode 值对比大小。

teledb=# CREATE OR REPLACE PROCEDURE p_collate_unicode() AS
$$
DECLARE  
    v_txt1 TEXT COLLATE "C" := '严';   
    v_txt2 TEXT COLLATE "C" := '丰';   
BEGIN
IF v_txt1 > v_txt2 THEN
    RAISE NOTICE ' % > % ',v_txt1,v_txt2;
ELSE
    RAISE NOTICE ' % > % ',v_txt2,v_txt1;
END IF; 
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
teledb=# CALL p_collate_unicode();
NOTICE:   丰 > 严 
CALL
teledb=#

teledb=# select '严'::bytea;
  bytea   
----------
 \xe4b8a5
(1 row)

teledb=# select '丰'::bytea;
  bytea   
----------
 \xe4b8b0
(1 row)

按汉字的拼音对比大小。

teledb=# CREATE OR REPLACE PROCEDURE p_collate_pinyin() AS
$$
DECLARE  
    v_txt1 TEXT COLLATE "zh_CN.utf8" := '严';   
    v_txt2 TEXT COLLATE "zh_CN.utf8" := '丰';   
BEGIN
    IF v_txt1 > v_txt2 THEN
    RAISE NOTICE ' % -> % ',v_txt1,v_txt2;
    ELSE
    RAISE NOTICE ' % -> % ',v_txt2,v_txt1;
    END IF; 
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
teledb=# CALL p_collate_pinyin();                       
NOTICE:   严 -> 丰 
CALL
teledb=#

变量赋值

teledb=# CREATE OR REPLACE PROCEDURE p_setval()  AS
$$
DECLARE  
    --定义时赋值
    v_int1 integer = 1; 
    --使用 :=兼容于plsql
    v_int2 integer := 1; 
    v_txt1 text;
    v_float float8;
    --使用查询赋值
    v_relname text = (select relname FROM pg_class LIMIT 1);  
    v_relpages integer;
    v_rec RECORD;
BEGIN         
    --在函数体中赋值
    v_txt1 = 'teledb_pg';  
    v_float = random();
    --使用查询赋值的另一种方式
    SELECT relname,relpages INTO v_relname,v_relpages FROM  pg_class ORDER BY random() LIMIT 1;
    RAISE NOTICE 'v_relname = % , relpages = %',v_relname,v_relpages;
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
teledb=# CALL p_setval();
NOTICE:  v_relname = pg_toast_17220_index , relpages = 1
CALL
teledb=#

BULK COLLECT

BULK COLLECT 将一个查询结果集保存起来。

示例1

teledb=# create table t5(f1 integer,f2 varchar(10));
CREATE TABLE
teledb=# insert into t5 values(1,'teledb_pg1'); 
INSERT 0 1
teledb=# insert into t5 values(2,'teledb_pg2'); 
INSERT 0 1

teledb=# create or replace procedure p_bulk_collect()
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 p_bulk_collect();
NOTICE:  teledb_pg1
NOTICE:  teledb_pg2
CALL

示例2

teledb=# create table tbl_person(id integer, name text, tdd int);
CREATE TABLE
teledb=# insert into tbl_person values(1,'teledb_pg',1);
insert into tbl_person values(2,'pgxz',1);
INSERT 0 1
teledb=# insert into tbl_person values(2,'pgxz',1);
INSERT 0 1
teledb=# insert into tbl_person values(3,'pg',2);
INSERT 0 1

teledb=# create or replace procedure p_bulkcollect_select_into(a_tdd integer) AS 
$$
declare      
   type TAPersonlist is table of tbl_person%rowtype;      
   vpa TAPersonlist;
   rp tbl_person%rowtype;
begin      
   select * bulk collect into vpa from tbl_person where tdd = a_tdd;
   raise notice 'count=%', vpa.count;
   for i in vpa.first..vpa.last loop
       rp = vpa[i];
       raise notice 'loop=%', i;
       raise notice 'vname=%', rp.name;
   end loop;
   raise notice 'vpa.count=%',vpa.count;
end;
$$language plpgsql;
CREATE PROCEDURE
teledb=# CALL p_bulkcollect_select_into(1);
NOTICE:  count=2
NOTICE:  loop=1
NOTICE:  vname=teledb_pg
NOTICE:  loop=2
NOTICE:  vname=pgxz
NOTICE:  vpa.count=2
CALL
teledb=#