PL/pgSQL编写postgresql函数

目录基本语句1 赋值赋值运算符 := 或 =2 单一行结果返回SELECT...INTO 语法赋值更新操作结果返回3 多行结果返回方式一：使用表充当容器方式二：使用自定义TYPE充当容器方式三：return query进行结果整体写入容器方式四：return setof record返回4 无结果执行5 执行动态命令2.1 语法2.2示例准备2.3 例...

柳树叶

2070人浏览 · 2022-05-16 16:53:38

柳树叶 · 2022-05-16 16:53:38 发布

方式三：return query进行结果整体写入容器

方式四：return setof record返回

基本语句

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

向您推荐>>Eolink开发者社区

权威｜前沿｜技术｜干货｜国内首个API全生命周期开发者社区

更多推荐

ELK实现containerd的容器日志采集展示【基于logging的全栈监测】

企业级ELK Stack构建介绍

云原生

深入理解 Mocha 测试框架：从零实现一个 Mocha

前言什么是自动化测试自动化测试在很多团队中都是Devops环节中很难执行起来的一个环节，主要原因在于测试代码的编写工作很难抽象，99%的场景都需要和业务强绑定，而且写测试代码的编写工作量往往比编写实际业务代码的工作量更多。在一些很多业务场景中投入产出比很低，适合写自动化测试的应该是那些中长期业务以及一些诸如组件一样的基础库。自动化测试是个比较大的概念，其中分类也比较多，比如单元测试，端对端测试，集

云原生

(20200916 Solved)docker-compose up创建容器自动退出

问题描述如题，创建容器后自动退出了。并且docker start container无效解决方案原因是缺失了控制终端的配置，需要在docker-compose.yml中增加tty:true ，有时候这样也不行，需要再增加一个command:/bin/bash，命令不一定是这个，需要是一个不会退出的命令，然后用-d后台启动容器。Referencesdocker-compose启动容器后自动退出...

云原生

所有评论(0)

查看更多评论

柳树叶

@qq_45143653

已为社区贡献1条内容