目錄

基本陳述句

1 賦值

賦值運算子 := 或 =

2 單一行結果回傳

SELECT...INTO 語法賦值

更新操作結果回傳

3 多行結果回傳

方式一：使用表充當容器

方式二：使用自定義TYPE充當容器

方式三：return query進行結果整體寫入容器

方式四：return setof record回傳

4 無結果執行

5 執行動態命令

2.1 語法

2.2示例準備

2.3 例子

6 列印輸出語法

---

基本陳述句

1 賦值

賦值運算子 := 或 =

語法：variable { := | = } expression;

例子：

create or replace function set_value() returns void as

$$

declare

v_id int;

v_name varchar;

begin

v_id := 100;

raise notice 'v_id=%',v_id;

v_name = '瀚高' ;

raise notice 'v_name=%',v_name;

end

$$

language plpgsql

列印輸出結果：

NOTICE: 00000: v_id=100

NOTICE: 00000: v_name=瀚高

2 單一行結果回傳

SELECT...INTO 語法賦值

語法: SELECT select_expressisons INTO target FROM ...;

SELECT INTO target select_expressisons FROM ...;

SELECT select_expressisons FROM ... INTO target ;

其中，變數target可以為基本資料變數、行變數與記錄變數，

例子：

create or replace function fn_record(v_id int) returns text as

$$

declare

v_record RECORD;

begin

select id,name,age into v_record from t_test where id=v_id ;

raise notice 'v_record=%',v_record; --1號位

select into v_record id,name,age from t_test where id=v_id ;

raise notice 'v_record=%',v_record; --2號位

select id,name,code from t_test where id=v_id into v_record;

raise notice 'v_record=%',v_record; --3號位

return v_record.id ||v_record.name;

end

$$

language plpgsql

列印輸出結果：

NOTICE: 00000: v_record=(1,張三,18) --1號位

NOTICE: 00000: v_record=(1,張三,18) --2號位

NOTICE: 00000: v_record=(1,張三,10010-11) --3號位

更新操作結果回傳

INSERT ... RETURNING expressions INTO [STRICT] target;

UPDATE ... RETURNING expressions INTO [STRICT] target;

DELETE ... RETURNING expressions INTO [STRICT] target;

例子：

create or replace function fn_into() returns void as

$$

declare

v_record RECORD;

begin

insert into t_test(name,age,code) values ('瀚高1',15,'10010-01')

returning name,age,code into v_record;

raise notice 'insert--v_record=%',v_record;

update t_test set name='瀚高2' where name='張三'

returning name,age,code into v_record;

raise notice 'update--v_record=%',v_record;

delete from t_test where name='李四'

returning name,age,code into v_record;

raise notice 'delete--v_record=%',v_record;

end

$$

language plpgsql

列印執行結果：

NOTICE: 00000: insert--v_record=(瀚高1,15,10010-01) 插入的資料

NOTICE: 00000: update--v_record=(瀚高2,18,10010-11) 更新后的資料

NOTICE: 00000: delete--v_record=(李四,20,10010-12) 被洗掉的資料

3 多行結果回傳

關鍵字 setof

例子：

create table t_test(id serial,name varchar,age int,code text);

insert into t_test(name,age,code) values('張三',18,'10010-11'),

('李四',20,'10010-12'),

('王五',22,'10010-13');

方式一：使用表充當容器

create table v_user as select * from t_test limit 0; --表型別容器

create or replace function fn_get_values() returns setof v_user as

$$

declare

var_user v_user%rowtype;

begin

for var_user in select * from t_test where id != 1 loop

return next var_user;

end loop;

return ;

end

$$

language plpgsql

方式二：使用自定義TYPE充當容器

create type v_users as (id int,name varchar,code text); --自定義型別容器

create or replace function fn_get_value() returns setof v_users as

$$

declare

var_user v_users%rowtype;

begin

for var_user in select id,name,code from t_test where id != 1 loop

return next var_user;

end loop;

return ;

end

$$

language plpgsql

方式三：return query進行結果整體寫入容器

create unlogged table v_user as select * from t_test limit 0; --表型別容器

create type v_user as (id int,name varchar,code text); --自定義型別容器

create or replace function fn_manyrow() returns setof v_user as

$$

begin

return query select * from t_test where id != 1;

end

$$

language plpgsql

方式四：return setof record回傳

例子

create or replace function fn_manyrow1()

RETURNS SETOF RECORD as $$

declare

r record;

begin

return query(select id,name,age from t_test);

end;

$$language plpgsql;

呼叫

select * from fn_manyrow1() as t(id int,name varchar ,age int);

呼叫函式執行結果

test_db=# select * from fn_manyrow1() as t(id int,name varchar ,age int);

id | name | age

----+-------+-----

3 | 王五 | 22

4 | 瀚高1 | 15

1 | 瀚高2 | 18

(3 行記錄)

注意使用record回傳時函式呼叫，不在是之前的select * from XXX,而是 select * from XXX as(col1 datatype1,col2 datatype2)這種，我們之前講過，record沒有固定結構，所以我們需要在查詢時指定record結果以一定格式展示，

要求：as 后欄位型別順序與record中存放的結果資料型別順序一致，

4 無結果執行

如果不需要接收執行結果，使用PERFORM 替換select，否則有執行結果回傳會報錯，

例子：

create or replace function fn_perform() returns void as

$$

begin

select current_time;

end

$$

language plpgsql

報錯如下：原因是select current_time; 有回傳值，但沒有使用變數接收，

test_db=# select fn_perform();

ERROR: 42601: query has no destination for result data

提示: If you want to discard the results of a SELECT, use PERFORM instead.

背景: PL/pgSQL function fn_perform() line 4 at SQL statement

上述例子修改如下：使用perform 替換select

create or replace function fn_perform() returns void as

$$

begin

perform current_time;

end

$$

language plpgsql

5 執行動態命令

2.1 語法

EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ... ] ];

關鍵字：EXECUTE

command-string 執行命令串 （SQL陳述句也是一條命令）

USING 運算式提供插入到該命令中的值

2.2示例準備

create table t_user(

id varchar default sys_guid() ,name varchar,

age int,code text)

partition by list(substring(code,1,2));

create table t_user_10 partition of t_user for values in ('10');

create table t_user_11 partition of t_user for values in ('11');

create table t_user_12 partition of t_user for values in ('12');

alter table t_user_10 add constraint pk_t_user_10_id primary key(id);

alter table t_user_11 add constraint pk_t_user_11_id primary key(id);

alter table t_user_12 add constraint pk_t_user_12_id primary key(id);

insert into t_user(name,age,code) values('張三',18,'10010-11');

insert into t_user(name,age,code) values('李四',18,'11010-11');

insert into t_user(name,age,code) values('王五',18,'12010-11');

2.3 例子

create or replace function fn_execute(partition_code varchar,uid varchar) returns text as

$$

declare

var_name text;

var_sql text;

begin

raise notice '=====%','select * from t_user_'||partition_code||' where id = '''||uid||''';';

var_sql := 'select name from t_user_'||partition_code||' where id = '''||uid||''';';

EXECUTE var_sql into var_name;

return var_name;

end

$$

language plpgsql

在上面例子準備中我們知道有一張用戶基表，它有三張磁區子表，磁區條件是code欄位值得前兩位，

需求：根據磁區標識值及資料主鍵快速定位某條資料，

如右函式：

輸入引數為磁區數值parttion_code和資料主鍵值uid，由于我們創建磁區表時表名是有規律的，每個磁區子表表名就是基表表名加上了磁區值，所以撰寫一個統一函式進行動態SQL執行，

注意：使用EXECUTE時，如果要與INTO寫值連用，語法為 EXECUTE sql_string INTO var_value

6 列印輸出語法

語法： RAISE level 'format' [, expression [, ...]];

RAISE 是報告資訊和拋出錯誤的陳述句

level 表示訊息級別（debug、log、info、notice、warning、exception）

‘format’ 訊息內容使用’’引起來，format是訊息內容，其中使用%作為占位符（如果要在format中表示%本身，需寫成%%），

[, expression [, ...]] 為%占位符的變數，依次用“,”排列，

例子：

例子：

RAISE NOTICE 'Calling cs_create_job(%)',v_job_idr;

--v_job_id變數的值將替換format中的%，

raise log '這是個日志訊息'; --輸出在日志檔案中

raise info '這個是一個資訊'; --以下幾個資訊列印在控制臺

raise notice '這是個提示訊息';

raise warning '這是個警告訊息';

raise exception '這是個例外訊息';