目录

基本语句

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 '这是个异常消息';

?

?

?

?

?

?