目录结构

---

---

---

文章快速说明索引

学习目标：

熟练掌握PostgreSQL数据库多种语言下函数的使用方法以及特殊技巧等

---

学习内容：（详见目录）

1、基于SQL 和 PL/PGSQL的PostgreSQL函数
2、涉及服务器编程相关的支撑语言的PostgreSQL函数
3、兼容适配其他数据库以及去O的相关内容

---

学习时间：

2020年9月5日10:46:31 - 2020年10月1日20:26:37

---

学习产出：

1、PostgreSQL数据库函数实践
2、CSDN 技术博客 1篇

---

前面我们在PostgreSQL的学习心得和知识总结（十三）|数据库配置参数说明（全网最详细 没有之一 建议收藏）里面已经详细地探讨过Python Perl Tcl三种 服务器编程 相关的 configure 参数所支持的对应PostgreSQL函数。而我们今天学习的重点在于基于SQL 和 PL/PGSQL的PostgreSQL函数，而这也是可以适当改造之后灵活应用到前面三种（或其他PostgreSQL所支持的语言）函数上。

下面我们所有的学习环境是Centos7+PostgreSQL12.3：

postgres=# select version();version                                   
-----------------------------------------------------------------------------PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.1.0, 64-bit
(1 row)postgres=#
postgres=# select lanname from pg_language;lanname  
----------internalcsqlplpgsql
(4 rows)postgres=# create table t1 (id int,name varchar(16));
CREATE TABLE
postgres=# insert into t1 values (1,'Postgres');
INSERT 0 1
postgres=# insert into t1 values (2,'Oracle');
INSERT 0 1
postgres=# insert into t1 values (3,'Mysql');
INSERT 0 1
postgres=# select * from t1;id |   name   
----+----------1 | Postgres2 | Oracle3 | Mysql
(3 rows)postgres=#

---

基于sql的函数实践

---

从上面select lanname from pg_language;里面我们可以看到PostgreSQL所支持的语言有以下四种：

1、internal
2、c
3、sql
4、plpgsql

下面开始今天的第一个重点内容：基于sql的函数

---

基于sql的函数返回值

---

sql 语言实现的函数 其函数体里面包含任意数量（即：多条SQL语句列表）的查询(增删改查)，但是该函数只返回最后一个查询且它还 必须 是 select的结果：如果返回的不是结果集，那么只返回最后一条查询结果的第一行；如果最后一个查询不返回任何行，那么该函数将返回 null 值；如果需要该函数返回最后一条 select 语句的所有行，则可以将函数的返回值定义为集合 即 setof mytype。各种示例如下所示：

1、返回最后一条查询结果的第一行 (需要做到返回值类型与函数定义的类型匹配)

postgres=# create or replace function func1() returns int as $$
postgres$#     select * from t1;
postgres$# $$ language sql;
2020-09-09 11:34:39.872 CST [2945] ERROR:  return type mismatch in function declared to return integer
2020-09-09 11:34:39.872 CST [2945] DETAIL:  Final statement must return exactly one column.
2020-09-09 11:34:39.872 CST [2945] CONTEXT:  SQL function "func1"
2020-09-09 11:34:39.872 CST [2945] STATEMENT:  create or replace function func1() returns int as $$select * from t1;$$ language sql;
ERROR:  return type mismatch in function declared to return integer
DETAIL:  Final statement must return exactly one column.
CONTEXT:  SQL function "func1"
postgres=# 
postgres=# 
postgres=# create or replace function func1() returns int as $$select id from t1;
$$ language sql;
CREATE FUNCTION
postgres=# 
postgres=# select * from func1();func1 
-------1
(1 row)postgres=#
postgres=# create TYPE mydictype as (id int,name varchar(16));
CREATE TYPE
postgres=# 
postgres=# create or replace function func1() returns mydictype as $$select * from t1;
$$ language sql;
CREATE FUNCTION
postgres=# 
postgres=# select * from func1();id |   name   
----+----------1 | Postgres
(1 row)postgres=#

2、除非函数声明为返回 void,否则最后一条语句必须是 select。报错: Function's final statement must be SELECT or INSERT/UPDATE/DELETE RETURNING.

postgres=# create or replace function func2() returns mydictype as $$select * from t1;insert into t1 values (4,'Redis')
$$ language sql;
2020-09-09 11:44:18.303 CST [2945] ERROR:  return type mismatch in function declared to return mydictype
2020-09-09 11:44:18.303 CST [2945] DETAIL:  Function's final statement must be SELECT or INSERT/UPDATE/DELETE RETURNING.
2020-09-09 11:44:18.303 CST [2945] CONTEXT:  SQL function "func2"
2020-09-09 11:44:18.303 CST [2945] STATEMENT:  create or replace function func2() returns mydictype as $$select * from t1;insert into t1 values (4,'Redis')$$ language sql;
ERROR:  return type mismatch in function declared to return mydictype
DETAIL:  Function's final statement must be SELECT or INSERT/UPDATE/DELETE RETURNING.
CONTEXT:  SQL function "func2"
postgres=# 
postgres=# 
postgres=# 
postgres=# create or replace function func2() returns mydictype as $$insert into t1 values (4,'Redis');update t1 set name = 'POSTGRESQL' where id = 1;select * from t1
$$ language sql;
CREATE FUNCTION
postgres=# 
postgres=# select func2();func2    
------------(2,Oracle)
(1 row)postgres=# select * from t1;id |    name    
----+------------2 | Oracle3 | Mysql4 | Redis1 | POSTGRESQL
(4 rows)postgres=#

3、如果最后一条 select 不返回任何结果,则返回空。如果函数声明为返回 void，那么里面可以任意写 （最后一行可以不是select ），其最终结果返回空

postgres=# drop function func2;
DROP FUNCTION
postgres=# create or replace function func2() returns mydictype as $$select * from t1 where id=1024;
$$ language sql;
CREATE FUNCTION
postgres=# 
postgres=# select func2();func2 
-------(1 row)postgres=# create or replace function func3() returns void as $$select 1+1;
$$ language sql;
CREATE FUNCTION
postgres=# select func3();func3 
-------(1 row)postgres=#

4、如果要返回结果集，需要加 setof 关键字，即 return setof mytype

postgres=# create or replace function func2() returns setof mydictype as $$select * from t1;
$$ language sql;
CREATE FUNCTION
postgres=# 
postgres=# select * from t1;id |    name    
----+------------2 | Oracle3 | Mysql4 | Redis1 | POSTGRESQL
(4 rows)postgres=# select func2();func2      
----------------(2,Oracle)(3,Mysql)(4,Redis)(1,POSTGRESQL)
(4 rows)postgres=#

5、通过 returning 的方式返回，即：INSERT/UPDATE/DELETE RETURNING

postgres=# select * from t1;id |    name    
----+------------2 | Oracle3 | Mysql4 | Redis1 | POSTGRESQL
(4 rows)postgres=# create or replace function func2() returns setof mydictype as $$delete from t1 returning *;
$$ language sql;
CREATE FUNCTION
postgres=# 
postgres=# select func2();func2      
----------------(2,Oracle)(3,Mysql)(4,Redis)(1,POSTGRESQL)
(4 rows)postgres=# select * from t1;id | name 
----+------
(0 rows)postgres=## 注: 如果上面不加 setof 则返回第一条数据postgres=# select * from t1;id |   name   
----+----------1 | Postgres2 | Oracle3 | Mysql
(3 rows)postgres=# create or replace function func3() returns setof mydictype as $$insert into t1 values (4,'Redis') returning *;
$$ language sql;
CREATE FUNCTION
postgres=# 
postgres=# select func3();func3   
-----------(4,Redis)
(1 row)postgres=# select * from t1;id |   name   
----+----------1 | Postgres2 | Oracle3 | Mysql4 | Redis
(4 rows)postgres=#

6、返回与表结构一样的复合类型(或者其集合)

postgres=# select * from t1;id |   name   
----+----------1 | Postgres2 | Oracle3 | Mysql4 | Redis
(4 rows)postgres=# \d t1Table "public.t1"Column |         Type          | Collation | Nullable | Default 
--------+-----------------------+-----------+----------+---------id     | integer               |           |          | name   | character varying(16) |           |          | postgres=# 
postgres=# create or replace function func4() returns setof t1 as $$select * from t1;                             
$$ language sql;
CREATE FUNCTION
postgres=# 
postgres=# select * from func4();id |   name   
----+----------1 | Postgres2 | Oracle3 | Mysql4 | Redis
(4 rows)postgres=##------------------------------------------------------------------------------#postgres=# select * from t1;id |   name   
----+----------1 | Postgres2 | Oracle3 | Mysql4 | Redis
(4 rows)postgres=# create or replace function func1() returns table(newid int,newname varchar(16)) as $$select * from t1;
$$ language sql;
CREATE FUNCTION
postgres=# 
postgres=# select * from func1();newid | newname  
-------+----------1 | Postgres2 | Oracle3 | Mysql4 | Redis
(4 rows)postgres=# \dList of relationsSchema | Name | Type  | Owner 
--------+------+-------+-------public | t1   | table | uxdb
(1 row)postgres=#

如上，通过指定 returns setof t1(和表同名)的方式来处理。注：这里的returns setof t1并不是返回t1表，实际上是使用了与t1表结构一样的复合类型。PostgreSQL在建表后会默认创建一个和表结构一样的同名复合类型。当然也可以返回表table(newid int,newname varchar(16))，但是在执行完select之后 这个表并非是被创建的（起一个临时作用）。

7、同访问复合类型一样，注意需要添加括号

postgres=# select * from t1;id |   name   
----+----------1 | Postgres2 | Oracle3 | Mysql
(3 rows)postgres=#  create table t2(test_value t1,time timestamp );
CREATE TABLE
postgres=#  insert into t2 values((4,'Redis'),now());
INSERT 0 1
postgres=# select * from t2;test_value |            time            
------------+----------------------------(4,Redis)  | 2020-09-30 13:46:25.426698
(1 row)postgres=# select (test_value).id,(test_value).name,time from t2;id | name  |            time            
----+-------+----------------------------4 | Redis | 2020-09-30 13:46:25.426698
(1 row)postgres=#

如上使用了创建 t1 这个表的时候的复合类型，在访问这种类型变量的时候 需要使用().

最后对函数返回值及其类型小结一下：

1、注意匹配返回值与定义时候的类型
2、sql 函数的函数体就是一个用分号分隔的 sql 语句列表，其中最后一条语句之后的分号是可选的
3、除非函数声明为返回 void,否则最后一条语句必须是 select
4、在 sql 函数中，不仅可以包含 select 查询语句，也可以包含 insert、update 和 delete 等其他标准的 sql 语句。但是和
transaction相关的语句不能包含其中，如 begin、commit、rollback、savepoint 和 execute 等

---

基于sql的函数的参数

---

上面说了函数的返回值，接下来看一下sql函数中的参数传递。在PostgreSQL中，$1 表示第一个参数，$2 为第二个参数并以此类推。如果参数是复合类型，则可以使用点表示法 即$1.name 访问复合类型参数中的 name 字段。

需要注意的是函数参数只能用作数据值，而不能用于标识符（如下的表名就不可以这么做）：

postgres=# select * from t1;id |   name   
----+----------1 | Postgres2 | Oracle3 | Mysql4 | Redis
(4 rows)postgres=# create or replace function func1(tablename varchar(16)) returns void as $$insert into $1 values(5,'Edb');
$$ language sql;
2020-09-09 17:42:47.822 CST [2695] ERROR:  syntax error at or near "$1" at character 92
2020-09-09 17:42:47.822 CST [2695] STATEMENT:  create or replace function func1(tablename varchar(16)) returns void as $$insert into $1 values(5,'Edb');$$ language sql;
ERROR:  syntax error at or near "$1"
LINE 2:     insert into $1 values(5,'Edb');^
postgres=# 
postgres=# create or replace function func1(v1 int,v2 varchar(16)) returns void as $$insert into t1 values($1,$2);insert into t1 values(v1,v2);
$$ language sql;
CREATE FUNCTION
postgres=# 
postgres=# select func1(5,'Edb');func1 
-------(1 row)postgres=# select * from t1;id |   name   
----+----------1 | Postgres2 | Oracle3 | Mysql4 | Redis5 | Edb5 | Edb
(6 rows)postgres=#

在开始上面的函数中，函数都是没有传递参数的；而我们上面的这个就是传递了带有参数名和参数类型的两个参数：(v1 int,v2 varchar(16))。

如上，因为我们在创建函数的时候明确的指定了两个参数的名称：v1 v2，于是在后面使用参数的时候就可以直接使用v1 v2或者$1,$2来代指。如果不含参数名只有参数类型的话，那么我们只可以用$1 $2 这样的标识符来表示。但是为了增加可读性，通常我们可以为其声明别名（参数名）。

---

上面是借助于return进行返回值的返还，在PostgreSQL中 也是支持指定了输出参数：输出参数的真正价值在于它为函数提供了返回多个字段的途径, 调用方式没有改变,只是返回结果多出一列。，这样就不用再 return 的方式。作为规范，建议 out、in 写在参数最前面；当然也可以 sum out int，但是推荐 out sum int 这种写法。实例如下：

postgres=# create or replace function func1(v1 int,v2 int,v3 int,out sum int) as $$select $1+$2+$3              
$$ language sql;
CREATE FUNCTION
postgres=# 
postgres=# select func1(2,4,6);func1 
-------12
(1 row)postgres=# select * from func1(2,4,6);sum 
-----12
(1 row)postgres=#
postgres=# create or replace function func2(v1 int,v2 int,v3 int,out sum int,out multiply int) as $$select $1+$2+$3,$1*$2*$3
$$ language sql;
CREATE FUNCTION
postgres=# 
postgres=# select func2(2,4,6);func2  
---------(12,48)
(1 row)postgres=# select * from func2(2,4,6);sum | multiply 
-----+----------12 |       48
(1 row)postgres=#

下面来稍微看一下可变参数：

注：在postgresql 函数支持可变参数，就是参数模式关键字 variadic。但有很多的限制，看起来很美，用起来一点都不方便

示例如下：

postgres=# create or replace function vari_func(variadic int[]) returns void as $$
beginfor i in array_lower($1,1)..array_upper($1,1) loopraise notice '%', $1[i];end loop;
end;
$$ language plpgsql;
CREATE FUNCTION
postgres=# 
postgres=# select vari_func(VARIADIC '{2,3,4}'::int[]);
NOTICE:  2
NOTICE:  3
NOTICE:  4vari_func 
-----------(1 row)postgres=#

---

基于sql的函数的重载

---

我们在前面学习C++的时候：什么是函数重载：一组函数，其函数名相同，参数列表的个数或者类型不同，那么这一组函数就可以称作为函数重载。

在PostgreSQL函数中与这类似，有利必有弊 倘若有同名函数，在删除的时候就需要指明参数类型和个数来指定删除哪个函数。

postgres=# \df func1List of functionsSchema | Name  | Result data type |                 Argument data types                 | Type 
--------+-------+------------------+-----------------------------------------------------+------public | func1 | integer          | v1 integer, v2 integer, v3 integer, OUT sum integer | func
(1 row)postgres=# \sf func1
CREATE OR REPLACE FUNCTION public.func1(v1 integer, v2 integer, v3 integer, OUT sum integer)RETURNS integerLANGUAGE sql
AS $function$select $1+$2+$3
$function$
postgres=# create or replace function func1(v1 int,v2 int,v3 int,v4 int,out sum int,out multiply int) as $$select $1+$2+$3+v4,$1*$2*$3*v4
$$ language sql;
CREATE FUNCTION
postgres=# 
postgres=# select func1(2,4,6,8);func1   
----------(20,384)
(1 row)postgres=# select func1(2,4,6);func1 
-------12
(1 row)postgres=# drop function func1(v1 int,v2 int,v3 int,v4 int);
DROP FUNCTION
postgres=# \dfList of functionsSchema |          Name          | Result data type |                 Argument data types                 | Type 
--------+------------------------+------------------+-----------------------------------------------------+------public | func1                  | integer          | v1 integer, v2 integer, v3 integer, OUT sum integer | funcpublic | postgres_fdw_handler   | fdw_handler      |                                                     | funcpublic | postgres_fdw_validator | void             | text[], oid                                         | func
(3 rows)postgres=##------------------------------------得到一个函数的定义----------------------------------------#
postgres=# CREATE OR REPLACE FUNCTION public.func1(v1 integer, v2 integer, v3 integer, OUT sum integer)
postgres-#  RETURNS integer
postgres-#  LANGUAGE sql
postgres-# AS $function$
postgres$#     select $1+$2+$3
postgres$# $function$;
CREATE FUNCTION
postgres=# 
postgres=# \dfList of functionsSchema | Name  | Result data type |                 Argument data types                 | Type 
--------+-------+------------------+-----------------------------------------------------+------public | func1 | integer          | v1 integer, v2 integer, v3 integer, OUT sum integer | func
(1 row)postgres=# \sf func1
CREATE OR REPLACE FUNCTION public.func1(v1 integer, v2 integer, v3 integer, OUT sum integer)RETURNS integerLANGUAGE sql
AS $function$select $1+$2+$3
$function$
postgres=# 
postgres=# select pg_get_functiondef('func1'::regproc);pg_get_functiondef                                      
----------------------------------------------------------------------------------------------CREATE OR REPLACE FUNCTION public.func1(v1 integer, v2 integer, v3 integer, OUT sum integer)+RETURNS integer                                                                            +LANGUAGE sql                                                                               +AS $function$                                                                               +select $1+$2+$3                                                                         +$function$                                                                                  +(1 row)postgres=#

---

基于pl/pgsql的函数

---

pl/pgsql 是 PostgreSQL 数据库系统的一个可加载的过程语言。同时它也是一种程序语言，叫做过程化SQL语言(Procedural Language/ Postgres SQL)。由此可见，它是PostgreSQL 数据库对SQL语言的扩展。在普通SQL语句的使用上增加了编程语言的特点，所以pl/pgsql就是把数据操作和查询语句组织在pl/pgsql代码的过程性单元中，通过逻辑判断、循环等操作实现复杂的功能或者计算的程序语言。其设计目标是创建一种可加载的过程语言，可以用于创建函数和触发器过程，为 sql 语言增加控制结构，执行复杂的计算。继承所有用户定义类型、函数、操作符 定义为被服务器信任的语言。pl/pgsql创建的函数可以在那些使用内置函数一样的情形下使用。比如，可以创建复杂的条件计算函数，并随后将之用于定义操作符或者用于函数索引中。

---

pl/pgsql的语法块结构

---

pl/pgsql 是一个块结构语言，函数定义的所有文本内容都必须是一个块。一个块最常用的语法定义格式如下：

[ <<label>> ]
[ declaredeclarations ]
beginstatements
end [ label ];# 注：这里的[] 意指可以省略

pl/pgsql 完整的程序由三个部分组成：声明部分、执行部分、异常处理部分。 其语法格式如下：

declare
--声明部分: 在此声明 pl/sql 用到的变量,类型及游标.
begin
-- 执行部分: 过程及 sql 语句,即程序的主要部分
exception
-- 执行异常部分: 错误处理
end;

如上的函数体代码块实际上为一个字符串，可以用美元符引用：$$书写字符串常量。$$中间可以包含标签名，可以自由命名，但是不能以数字开头，比如可以命名为$body$、$_$等等，但是该标签名必须成对出现，且大小写敏感。

解释一下：

1、执行部分不能省略，声明部分和异常处理部分可以
2、块中的每一个 declaration 和每一条 statement 都由一个分号终止
3、块支持嵌套：嵌套时子块的 end 后面必须跟一个分号，最外层的块 end 后可不跟分号
4、begin 后面不能跟分号，end 后跟的 标签名 必须和块开始时的标签名一致
5、声明的变量在当前块及其子块中有效，子块开始前可 声明并覆盖 (只在子块内覆盖)外部块的同名变量
6、变量被子块中声明的变量覆盖时，子块可以通过外部块的 label 访问外部块的变量
7、在函数中，使用双减号加注释，它的作用范围是只能在一行有效；而使用 /* */ 来加一行或多行注释

下面来看一个把上面这几点都包括入内的实例：

postgres=# CREATE FUNCTION func2() RETURNS int AS $BODY$
<< outerblock >>
DECLAREnum int := 1;
BEGINRAISE NOTICE 'Num here is %', num;  -- Prints 1num := 2;---- Create a subblock--DECLAREnum int := 3;BEGINRAISE NOTICE 'Num here is %', num;  -- Prints 3RAISE NOTICE 'Outer num here is %', outerblock.num;  -- Prints 2END;RAISE NOTICE 'Num here is %', num;  -- Prints 2/* Hello World */RETURN num;
END outerblock;
$BODY$ LANGUAGE plpgsql;
CREATE FUNCTION
postgres=# 
postgres=# select func2();
NOTICE:  Num here is 1
NOTICE:  Num here is 3
NOTICE:  Outer num here is 2
NOTICE:  Num here is 2func2 
-------2
(1 row)postgres=#

---

pl/pgsql块结构之匿名块

---

注：do 语句不属于块结构，但是我们提出借助于它来执行匿名块。

postgres=# do $$
<<FIRST>>
declarenum int := 0;
beginRAISE info '***********************'; RAISE NOTICE 'Num here is %', num;  -- Prints 0num := 2;/*** Create a subblock*/<<SECOND>>DECLAREnum int := 3;BEGINRAISE NOTICE 'Num here is %', num;  -- Prints 3RAISE NOTICE 'Second num here is %', SECOND.num;  -- Prints 3RAISE NOTICE 'Outer num here is %',FIRST.num; -- Prints 2 end SECOND;RAISE NOTICE 'Num here is %', num;  -- Prints 2/* Hello World */ RAISE info '***********************';
END FIRST
$$;
INFO:  ***********************
NOTICE:  Num here is 0
NOTICE:  Num here is 3
NOTICE:  Second num here is 3
NOTICE:  Outer num here is 2
NOTICE:  Num here is 2
INFO:  ***********************
DO
postgres=#

这里说明一下：

1、raise用于函数中打印输出，类似于oracle的dbms_output.putline();。其语法为：raise notice 'this is raise test %',param;
2、上面语句中的%为参数占位符，有多个参数时就添加多个%，不用考虑参数的数值类型
3、notice字段为级别，可以为debug/log/info/notice/warning/exception，这些级别的信息是直接写到服务端日志还是返回到客户端或是二者皆有，是由log_min_messages和client_min_messages两个参数控制，这两个参数在数据库初始化时用到。

# postgresql.conf#log_min_messages = warning     # values in order of decreasing detail:#   debug5#   debug4#   debug3#   debug2#   debug1#   info#   notice#   warning#   error#   log#   fatal#   panic#client_min_messages = notice       # values in order of decreasing detail:#   debug5#   debug4#   debug3#   debug2#   debug1#   log#   notice#   warning#   error

---

pl/pgsql块结构之父子块

---

在上面的这两个实例中都使用到了父子块： 指的是pl/pgsql 可以一个块在另一个块的主体中 。一个块嵌入在另一个块中称为子块，包含子块的块称为外部块。子块用于组织语句，这样大块能被分为更小和更多逻辑子块。子块的变量的名称可以与外部块变量名称同名，但是在实践中不建议这么做。当在子块中声明一个与外部变量同名的变量，外部变量在子块中被隐藏。如果需要访问外部块的变量，可以使用块标签作为变量的限定符。

注：因为上面两个里面都有用到，这里我们就不再展示具体的SQL实例了

---

pl/pgsql的变量与参数

---

pl/pgsql函数的变量声明

---

postgres=# CREATE FUNCTION func2() RETURNS int AS $BODY$
<< outerblock >>
DECLAREnum int := 1;
BEGINRAISE NOTICE 'Num here is %', num;  -- Prints 1num := 2;---- Create a subblock--DECLAREnum int := 3;BEGINRAISE NOTICE 'Num here is %', num;  -- Prints 3RAISE NOTICE 'Outer num here is %', outerblock.num;  -- Prints 2END;RAISE NOTICE 'Num here is %', num;  -- Prints 2/* Hello World */RETURN num;
END outerblock;
$BODY$ LANGUAGE plpgsql;
CREATE FUNCTION
postgres=# 
postgres=# select func2();
NOTICE:  Num here is 1
NOTICE:  Num here is 3
NOTICE:  Outer num here is 2
NOTICE:  Num here is 2func2 
-------2
(1 row)postgres=#

如上，我们来看这个实例，所有在块里使用的变量都必须在块的声明段里先进行声明（唯一的例外是 for 循环里的循环计数变量，该变量被自动声明为整型）。一个变量声明的语法格式如下：

DECLAREvariable_name [ constant ] variable_type [ not null ] [ { default | := } expression ];

下面来逐一解释一下：

1、sql 中的数据类型均可作为 pl/pgsql 函数中变量的数据类型。如 integer、varchar 、timestamp 和 char 等
2、如果给出了default子句：该变量在进入begin块时将被初始化为该默认值，否则被初始化为 sql 空值。缺省值是在每次进入该块时进行计算的。因此，如果把 now()赋予一个类型为timestamp 的变量，那么该变量的缺省值将为函数实际调用时的时间，而不是函数预编译时的时间
3、constant 选项是为了避免该变量在进入 begin 块后被重新赋值，以保证该变量为常量。和 C 一样
4、如果声明了 not null，那么赋予 null 数值给该变量将导致一个运行时错误。因此 所有声明为 not null 的变量也必须在声明时定义一个非空的缺省值

下面来看一下一些相对应的实例：

postgres=# CREATE FUNCTION func2() RETURNS void AS $$DECLAREnum int;BEGINRAISE NOTICE 'Num here is %', num;END;$$ LANGUAGE plpgsql;
CREATE FUNCTION
postgres=# 
postgres=# select func2();
NOTICE:  Num here is <NULL>func2 
-------(1 row)
# ------------------------------------------------------------------------------------------------ #
postgres=#
postgres=# CREATE FUNCTION func2() RETURNS void AS $$DECLAREtime1 timestamp default now();BEGINRAISE NOTICE 'Time1 here is %', time1;END;$$ LANGUAGE plpgsql;
CREATE FUNCTION
postgres=# 
postgres=# select func2();
NOTICE:  Time1 here is 2020-09-05 17:04:59.515041func2 
-------(1 row)
# ------------------------------------------------------------------------------------------------ #
postgres=# drop function func2;
DROP FUNCTION
postgres=# CREATE FUNCTION func2() RETURNS void AS $$DECLAREtime1 constant timestamp default now();BEGINtime1:=now();RAISE NOTICE 'Time1 here is %', time1;END;$$ LANGUAGE plpgsql;
2020-09-10 17:06:47.971 CST [2695] ERROR:  variable "time1" is declared CONSTANT at character 112
2020-09-10 17:06:47.971 CST [2695] STATEMENT:  CREATE FUNCTION func2() RETURNS void AS $$DECLAREtime1 constant timestamp default now();BEGINtime1:=now();RAISE NOTICE 'Time1 here is %', time1;END;$$ LANGUAGE plpgsql;
ERROR:  variable "time1" is declared CONSTANT
LINE 5:      time1:=now();RAISE NOTICE 'Time1 here is %', time1;^
# ------------------------------------------------------------------------------------------------ #         
postgres=# CREATE FUNCTION func2() RETURNS void AS $$DECLAREtime1 timestamp not null;BEGINRAISE NOTICE 'Time1 here is %', time1;END;$$ LANGUAGE plpgsql;
2020-09-10 17:07:55.811 CST [2695] ERROR:  variable "time1" must have a default value, since it's declared NOT NULL at character 75
2020-09-10 17:07:55.811 CST [2695] STATEMENT:  CREATE FUNCTION func2() RETURNS void AS $$DECLAREtime1 timestamp not null;BEGINRAISE NOTICE 'Time1 here is %', time1;END;$$ LANGUAGE plpgsql;
ERROR:  variable "time1" must have a default value, since it's declared NOT NULL
LINE 3:      time1 timestamp not null;^
postgres=#

---

pl/pgsql函数的变量类型

---

对于变量的使用，我们接下来再来介绍一下三种特殊的变量类型：

1、拷贝类型
2、行类型
3、记录类型

1、 拷贝类型：variable %type 其中%type 表示一个变量或表字段的数据类型。pl/pgsql 允许通过该方式声明一个变量，其类型等同于 variable 或表字段的数据类型。见如下示例(t1表的 id 是 int 类型,name是 varchar(16) 类型)：

postgres=# \d+ t1 Table "public.t1"Column |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------id     | integer               |           |          |         | plain    |              | name   | character varying(16) |           |          |         | extended |              | 
Access method: heappostgres=# create or replace function func2(out Myid int,out Myname text)
as $$
declare
test_v_id t1.id%type :=10;
test_v_name t1.name%type :='song';
begin
Myid :=test_v_id * 10;
Myname :=test_v_name||'baobao';
end;
$$ language plpgsql;
CREATE FUNCTION
postgres=# 
postgres=# select * from func2();myid |   myname   
------+------------100 | songbaobao
(1 row)postgres=#

注：这种通过使用%type来定义变量的方式，一旦引用的变量类型今后发生改变，我们也无需修改该变量的类型声明。

如上，我们只是简单地在函数体内部使用。同理我们也可以在函数的参数和返回值中使用该方式的类型声明。如下是分别在函数的 入参 出参 入出参的实例展示：

postgres=# create or replace function func2(out Myid t1.id%type,out Myname t1.name%type)
as $$
declare
test_v_id int :=10;
test_v_name text :='song';
begin
Myid :=test_v_id * 10;
Myname :=test_v_name||'baobao';
end;
$$ language plpgsql;
NOTICE:  type reference t1.id%TYPE converted to integer
NOTICE:  type reference t1.name%TYPE converted to character varying
CREATE FUNCTION
postgres=# 
postgres=# select * from func2();myid |   myname   
------+------------100 | songbaobao
(1 row)postgres=# create or replace function func3(in Myid t1.id%type) returns text
as $$
declareMyname text;   
begin                     select name into Myname from t1 where id = Myid;return Myname;                 
end;                           
$$ language plpgsql;
NOTICE:  type reference t1.id%TYPE converted to integer
CREATE FUNCTION
postgres=# select * from func3(1);func3   
----------Postgres
(1 row)postgres=#
postgres=# create or replace function func4(in v_id int,out Myid t1.id%type,inout Myname t1.name%type)
as $$
beginMyid :=v_id * 10;Myname :=Myname || ' ' ||'hello world';
end;
$$ language plpgsql;
NOTICE:  type reference t1.id%TYPE converted to integer
NOTICE:  type reference t1.name%TYPE converted to character varying
CREATE FUNCTION
postgres=# 
postgres=# select * from func4(1,'songbaobao');myid |         myname         
------+------------------------10 | songbaobao hello world
(1 row)postgres=#

2、 行类型：table_name%rowtype 表示指定表的行类型。我们在创建一个表的时候，PostgreSQL也会随之创建出一个与之相应的复合类型，该类型名等同于表名。由此方式声明的变量，可以保存select 返回结果中的一行。如果要访问变量中的某个域字段，可以使用点表示法，如 t1.name。但是行类型的变量只能访问自定义字段，无法访问系统提供的隐含字段，如 oid 等。对于函数的参数，我们只能使用复合类型标识变量的数据类型。最后需要说明的是，推荐使用%rowtype的声明方式。因为这样可以具有更好的可移植性，因为在 oracle 的 pl/sql 中也存在相同的概念，其声明方式也为%rowtype。

但是不能在函数的入参里声明 rowtype 类型的参数，会报如下错误：

postgres=# create or replace function func2(aRow t1%rowtype) returns void as $$
postgres$# declare
postgres$#     id int;name text;
postgres$# begin
postgres$#     id := aRow.id * 2;
postgres$#     name :=aRow.name  || 'DataBase';
postgres$#     raise info 'id,name: %,%',id,name;
postgres$# end ;
postgres$# $$ language plpgsql;
ERROR:  syntax error at or near "%"
LINE 1: create or replace function func2(aRow t1%rowtype) returns vo...^
postgres=# 
postgres=# 
postgres=# create or replace function func2(aRow t1) returns void as $$
declareid int;name text;
beginid := aRow.id * 2;name :=aRow.name  || 'DataBase';raise info 'id,name: %,%',id,name;
end ;
$$ language plpgsql;
CREATE FUNCTION
postgres=# 
postgres=# select * from t1;id |   name   
----+----------1 | Postgres2 | Oracle3 | Mysql4 | Redis
(4 rows)postgres=# select func2 ((5,'Edb'));
INFO:  id,name: 10,EdbDataBasefunc2 
-------(1 row)postgres=# select func2(t1.*) from t1;
INFO:  id,name: 2,PostgresDataBase
INFO:  id,name: 4,OracleDataBase
INFO:  id,name: 6,MysqlDataBase
INFO:  id,name: 8,RedisDataBasefunc2 
-------(4 rows)postgres=#

无法访问系统提供的隐含字段，比如PostgreSQL里面的如下几个隐藏的字段：

postgres=# select a.relname,b.attname,b.atttypid from pg_class a join pg_attribute b on a.oid=b.attrelid and a.relname='t1';relname | attname  | atttypid 
---------+----------+----------t1      | tableoid |       26t1      | cmax     |       29t1      | xmax     |       28t1      | cmin     |       29t1      | xmin     |       28t1      | ctid     |       27t1      | id       |       23t1      | name     |     1043
(8 rows)postgres=# select a.attname from pg_attribute a where a.attrelid=(select oid from pg_class where relname='t1');attname  
----------cmaxcminctididnametableoidxmaxxmin
(8 rows)postgres=#

示例如下：

postgres=# create or replace function func2(aRow t1) returns void as $$
declareid int;name text;xmin int;
beginid := (aRow.id) * 2;xmin := (aRow).xmin;name :=(aRow.name)|| ' ' || 'DataBase';raise info 'id,name,xmin: %,%,%',id,name,xmin;
end ;
$$ language plpgsql;
CREATE FUNCTION
postgres=# 
postgres=# select func2(t1.*) from t1;
ERROR:  column "xmin" not found in data type t1
LINE 1: SELECT (aRow).xmin^
QUERY:  SELECT (aRow).xmin
CONTEXT:  PL/pgSQL function func2(t1) line 5 at assignment
postgres=# 

但是我要在这里特此说明一下，遇到了一个问题留给各位小伙伴，详细描述如下：

postgres=# select oid,relname,tableoid ,xmin from pg_class where relname='t1';oid  | relname | tableoid | xmin 
-------+---------+----------+------16398 | t1      |     1259 |  500
(1 row)postgres=#
postgres=# create or replace function func2(aRow t1) returns void as $$
declareid int;name text;xmin int;
beginid := (aRow.id) * 2;xmin := (aRow.xmin);name :=(aRow.name)|| ' ' || 'DataBase';raise info 'id,name,xmin: %,%,%',id,name,xmin;
end ;
$$ language plpgsql;
CREATE FUNCTION
postgres=# 
postgres=# select func2(t1.*) from t1;
INFO:  id,name,xmin: 2,Postgres DataBase,148
INFO:  id,name,xmin: 4,Oracle DataBase,140
INFO:  id,name,xmin: 6,Mysql DataBase,136
INFO:  id,name,xmin: 8,Redis DataBase,136func2 
-------(4 rows)postgres=#

我们详细看一下前后的func2变化在：

前面的是：xmin := (aRow).xmin;，而后面的是xmin := (aRow.xmin); 导致的结果不知道为什么有区别

OK，言归正传 我们来使用它来定义一个变量：

postgres=# create or replace function func2() returns t1 as $$
declareMyRow t1%rowtype;
beginselect * from t1 into MyRow ;return MyRow;
end ;
$$ language plpgsql;
CREATE FUNCTION
postgres=# 
postgres=# select * from func2();id |   name   
----+----------1 | Postgres
(1 row)postgres=#

3、 记录类型：记录变量类似于行类型变量，但是它们没有预定义的结构，只能通过select 或 for命令来获取实际的行结构。因此记录变量在被初始化之前无法访问，否则将引发运行时错误。注：record不是真正的数据类型，它只是一个占位符。

返回第一条数据：

postgres=# create or replace function func2() returns t1 as $$
declareMyRow record;
beginselect * from t1 into MyRow ;return MyRow;
end ;
$$ language plpgsql;
CREATE FUNCTION
postgres=# 
postgres=# select * from func2();id |   name   
----+----------1 | Postgres
(1 row)postgres=#

返回结果集：

postgres=# create or replace function func3() returns setof t1 as $$
declareMyRow record;t_sql text :='select * from t1';
beginfor MyRow in execute t_sql loopreturn next MyRow;end loop;
end;
$$ language plpgsql;
CREATE FUNCTION
postgres=# 
postgres=# select * from func3();id |   name   
----+----------1 | Postgres2 | Oracle3 | Mysql4 | Redis
(4 rows)postgres=# select func3();func3     
--------------(1,Postgres)(2,Oracle)(3,Mysql)(4,Redis)
(4 rows)postgres=## 当然也可以直接 execute 一个SQL语句：
postgres=# create or replace function func3() returns setof t1 as $$
declareMyRow record;                                
beginfor MyRow in execute 'select * from t1' loopreturn next MyRow;end loop;
end;
$$ language plpgsql;
CREATE FUNCTION
postgres=# 
postgres=# select func3();func3     
--------------(1,Postgres)(2,Oracle)(3,Mysql)(4,Redis)
(4 rows)postgres=## 当然如果连 execute 也不用的话也行，直接使用 select 的结果集
postgres=# create or replace function func3() returns setof t1 as $$
declareMyRow record;
beginfor MyRow in select * from t1 loopreturn next MyRow;end loop;
end;
$$ language plpgsql;
CREATE FUNCTION
postgres=# 
postgres=# select * from func3();id |   name   
----+----------1 | Postgres2 | Oracle3 | Mysql4 | Redis
(4 rows)postgres=#

返回某一列的数据：（注意类型匹配问题）

postgres=# create or replace function func3() returns setof text as $$
declareMyRow record;
beginfor MyRow in select * from t1 loopreturn next MyRow.name;end loop;
end;
$$ language plpgsql;
CREATE FUNCTION
postgres=# 
postgres=# select * from func3();func3   
----------PostgresOracleMysqlRedis
(4 rows)postgres=# drop function func3;
DROP FUNCTION
postgres=# create or replace function func3() returns setof text as $$
declareMyRow record;
beginfor MyRow in select * from t1 loopreturn next MyRow;end loop;
end;
$$ language plpgsql;
CREATE FUNCTION
postgres=# select * from func3();
ERROR:  wrong record type supplied in RETURN NEXT
DETAIL:  Returned type integer does not match expected type text in column 1.
CONTEXT:  PL/pgSQL function func3() line 6 at RETURN NEXT
postgres=#

如果需要在函数里返回 record 类型或 record 集合的话（需要注意函数调用问题)：

postgres=# create or replace function func3() returns setof record as $$
declareMyRow record;
beginfor MyRow in select * from t1 loopreturn next MyRow;end loop;
end;
$$ language plpgsql;
CREATE FUNCTION
postgres=# 
postgres=# select * from func3();
ERROR:  a column definition list is required for functions returning "record"
LINE 1: select * from func3();^
postgres=# select * from func3() as (myid int,myname varchar(16));myid |  myname  
------+----------1 | Postgres2 | Oracle3 | Mysql4 | Redis
(4 rows)postgres=## 注：需要这样调用,因为返回的 record 没有预先的结构

pl/pgsql函数的参数使用

---

1、 在函数声明的同时给出参数变量名

postgres=# create or replace function func2(v1 int,v2 int,v3 int,out sum int,out multiply int) as $$select $1+$2+v3,v1*$2*$3
$$ language sql;
CREATE FUNCTION
postgres=# 
postgres=# select func2(2,3,4);func2  
--------(9,24)
(1 row)postgres=#

2、在声明段中为参数变量定义别名

postgres=#  create TYPE mydictype2 as (sum int,multiply int);
CREATE TYPE
postgres=# create or replace function func2(int,int,int) returns mydictype2 as $$declarevalue1  alias for $1;beginreturn (value1+$2+$3,$1*$2*$3);end;
$$ language plpgsql;
CREATE FUNCTION
postgres=# 
postgres=# select func2(2,3,4);func2  
--------(9,24)
(1 row)postgres=#

3、在 declare 定义变量的时候声明默认值；给函数入参定义默认值

postgres=# create or replace function func2(int,int,int) returns mydictype2 as $$declarevalue1  alias for $1;tmp int default 10;beginreturn (value1+$2+$3+tmp,$1*$2*$3*tmp);end;
$$ language plpgsql;
CREATE FUNCTION
postgres=# 
postgres=# select func2(2,3,4);func2   
----------(19,240)
(1 row)postgres=#
# ------------------------------------------------------------------------------------------------ #
postgres=# drop function func2;
DROP FUNCTION
postgres=# create or replace function func2(int, int ,v3 int default 4) returns mydictype2 as $$declarevalue1  alias for $1;tmp int default 10;beginreturn (value1+$2+$3+tmp,$1*$2*$3*tmp);end;
$$ language plpgsql;
CREATE FUNCTION
postgres=# 
postgres=# select func2(2,3,5);func2   
----------(20,300)
(1 row)postgres=#postgres=# select func2(2,3);func2   
----------(19,240)
(1 row)postgres=## 注：如上 调用的时候可以传参也可以不传参，不传则使用默认值

4、可以给入参赋值；但不可以给出参赋默认初值

postgres=# create or replace function func2(in int, in int ,out v3 int default 4) returns mydictype2 as $$declarevalue1  alias for $1;tmp int default 10;beginv3=40;return (value1+$2+v3+tmp,$1*$2*v3*tmp);end;
$$ language plpgsql;
2020-09-11 09:37:38.203 CST [2695] ERROR:  only input parameters can have default values
2020-09-11 09:37:38.203 CST [2695] STATEMENT:  create or replace function func2(in int, in int ,out v3 int default 4) returns mydictype2 as $$declarevalue1  alias for $1;tmp int default 10;beginv3=40;return (value1+$2+v3+tmp,$1*$2*v3*tmp);end;$$ language plpgsql;
ERROR:  only input parameters can have default values
postgres=# 
postgres=# 
postgres=# create or replace function func2(in int, in int ,in v3 int default 4) returns mydictype2 as $$declarevalue1  alias for $1;tmp int default 10;beginv3=40;return (value1+$2+v3+tmp,$1*$2*v3*tmp);end;
$$ language plpgsql;
CREATE FUNCTION
postgres=# 
postgres=# select func2(2,3); func2   
-----------(55,2400)
(1 row)postgres=# drop function func2;
DROP FUNCTION
postgres=# create or replace function func2(in int, in int ,in v3 int default 4,out stringValue text) returns text as $$declarevalue1  alias for $1;tmp int default 10;beginv3=40;stringValue='songbaobao';end;
$$ language plpgsql;
CREATE FUNCTION
postgres=# select func2(2,3); func2    
------------songbaobao
(1 row)postgres=#

---

pl/pgsql函数的in/out参数

---

在上面的实例中可以看到有in out inout的多次使用，下面我们来详细看一下这三种参数的模式说明：

1、in 是参数的默认模式，这种模式就是在程序运行的时候已经具有值，在程序体中值不会改变
2、out 模式定义的参数只能在过程体内部赋值，表示该参数可以将某个值传递回调用它的过程
3、inout 表示该参数可以向该过程中传递值，也可以将某个值传出去

4、不建议用 inout 参数，用 in 和 out 即可，用 inout 可能会把情况搞复杂。如果要兼容 Oracle的 inout 可以这么写，不过要小心里面的些许差别

1、参数模式 in、out的使用

postgres=# create or replace function func2(in int, in int ,out v3 int)  as $$declarevalue1  alias for $1;tmp int default 10;beginv3=value1*$2*tmp;end;
$$ language plpgsql;
CREATE FUNCTION
postgres=# 
postgres=# 
postgres=# select func2(2,3);func2 
-------60
(1 row)postgres=#

2、参数模式 inout的使用

postgres=# select * from t1;id |   name   
----+----------1 | Postgres2 | Oracle3 | Mysql4 | Redis
(4 rows)postgres=# create or replace function func3 (inout v_id int default 3,inout v_ret text default 'song') as $$
postgres$# begin
postgres$#     select name from t1 where id = v_id into v_ret;
postgres$# end;
postgres$# $$ language plpgsql;
CREATE FUNCTION
postgres=# 
postgres=# select * from func3(5);v_id | v_ret 
------+-------5 | 
(1 row)postgres=# select * from func3();v_id | v_ret 
------+-------3 | Mysql
(1 row)postgres=# select * from func3(1);v_id |  v_ret   
------+----------1 | Postgres
(1 row)postgres=#

解释一下：

1、默认值是 3，若没有 5 这个结果，则把自己作为 out 输出了，即 5
2、若不传参，则和 in 一样，使用了默认值 3
3、若有 1 这个结果，则把 1 作为 out 输出

---

pl/pgsql函数的语句使用

---

pl/pgsql函数的赋值语句

---

1、定义时赋值：pl/pgsql 中赋值语句的形式为：identifier := expression等号两端的变量和表达式的类型或者一致或者可以通过 postgresql 的转换规则进行转换，否则将会导致运行时错误

postgres=#  create or replace function func3() returns void as $$
declareMytext text :='hello world';Myid int := '1';
beginraise info 'id,text: %,%',Myid,Mytext;
end;
$$ language plpgsql;
CREATE FUNCTION
postgres=# 
postgres=# select func3();
INFO:  id,text: 1,hello worldfunc3 
-------(1 row)postgres=#  create or replace function func3() returns void as $$
declareMytext text :='hello world';Myid int := 'l';
beginraise info 'id,text: %,%',Myid,Mytext;
end;
$$ language plpgsql;
CREATE FUNCTION
postgres=# select func3();
ERROR:  invalid input syntax for type integer: "l"
CONTEXT:  PL/pgSQL function func3() line 4 during statement block local variable initialization
postgres=#

2、 select into进行赋值：通过该语句可以为记录变量或行类型变量进行赋值，其语法格式为：select into target select_expressions from ...。该赋值方式一次只能赋值一个变量，表达式中的 target 可以表示为是一个记录变量、行变量或者是一组用逗号分隔的简单变量和记录/行字段的列表。select_expressions 以及剩余部分和普通 sql 一样。如果将一行或者一个变量列表用做目标，那么选出的数值必需精确匹配目标的结构，否则就会产生运行时错误。如果目标是一个记录变量，那么它自动将自己构造成命令结果列的行类型。如果命令返回零行，目标被赋予空值。如果命令返回多行，那么将只有第一行被赋予目标，其它行将被忽略。

在执行 select into 语句之后，可以通过检查内置变量 found 来判断本次赋值是否成功，如：

postgres=# create or replace function func3() returns record as $$
postgres$# declare
postgres$#     MyRow record;
postgres$# begin
postgres$#     select * from t1 into MyRow limit 1;
postgres$#     if not found then
postgres$#         raise exception 'error,no info found';
postgres$#     end if;
postgres$# return MyRow;
postgres$# end;         
postgres$# $$ language plpgsql;
CREATE FUNCTION
postgres=# 
postgres=# select func3();func3     
--------------(1,Postgres)
(1 row)postgres=#
postgres=# select * from  func3() as (myid int,myname varchar(16));myid |  myname  
------+----------1 | Postgres
(1 row)postgres=#

---

pl/pgsql函数的控制语句

---

if 和 case 语句让你可以根据某种条件执行命令 以达到分流导向的作用

在pl/pgsql中有三种形式的 if 条件语句：
1、if ... then ... end if;
2、if ... then ... else ... end if;
3、if ... then ... elsif ... then ... else ... end if;

---

两种形式的 case:
1、case ... when ... then ... else ... end case;
2、case when ... then ... else ... end case;

1、if 条件语句

if语句的语法格式详细如下：

if search_condition thenstatement_list
end if;if search_condition thenstatement_list
elsestatement_list
end if;if search_condition thenstatement_list
elsif search_condition thenstatement_list
elsestatement_list
end if;

示例如下：

postgres=# select * from t1;id |   name   
----+----------1 | Postgres2 | Oracle3 | Mysql
(3 rows)postgres=# create or replace function if_function(id_value int) returns void as $$
declare
dbname text;
begin
select name into dbname from t1 where id = id_value;
if dbname is null then
raise notice '没有这个数据库!';
elsif dbname = 'Oracle' then
raise notice '这个是要钱的';
else
raise notice '这个是开源的';
end if;
end;
$$ language plpgsql;
CREATE FUNCTION
postgres=# 
postgres=# select if_function(0);
NOTICE:  没有这个数据库!if_function 
-------------(1 row)postgres=# select if_function(2);
NOTICE:  这个是要钱的if_function 
-------------(1 row)postgres=# select if_function(1);
NOTICE:  这个是开源的if_function 
-------------(1 row)postgres=#

2、case 条件语句

下面是case语句的示例：

postgres=# select * from t1;id |   name   
----+----------1 | Postgres2 | Oracle3 | Mysql
(3 rows)postgres=# create or replace function case_test1(database text) returns void as $$
declare
message text;
x int;
begin
select id from t1 where name = $1 into x;
case x
when 1, 3 then
message := '它是开源的';
else
message := '人家收费的';
end case;
raise notice 'Your result is %' ,message;
end;
$$ language plpgsql;
CREATE FUNCTION
postgres=# 
postgres=# select case_test1('Mysql');
NOTICE:  Your result is 它是开源的case_test1 
------------(1 row)postgres=# select case_test1('Oracle');
NOTICE:  Your result is 人家收费的case_test1 
------------(1 row)postgres=##-----------------------------------------------------------------------------#postgres=# create or replace function case_test2(database text) returns void as $$
declaremessage text;x int;
beginselect id from t1 where name = $1 into x;case  when x in(1, 3) thenmessage := '它是开源的';elsemessage := '人家收费的';end case;raise notice 'Your result is %' ,message;
end;
$$ language plpgsql;
CREATE FUNCTION
postgres=# 
postgres=# select case_test2('Mysql');
NOTICE:  Your result is 它是开源的case_test2 
------------(1 row)postgres=# select case_test2('Oracle');
NOTICE:  Your result is 人家收费的case_test2 
------------(1 row)postgres=#

---

pl/pgsql函数的循环语句

---

在pl/pgsql中的循环主要是以下三种：

1、loop 简单循环
2、while 循环
3、for 循环

1、loop 简单循环语句

其语法格式如下：

[ <<label>> ]
loopstatementsexit [ label ] [ when boolean-expression ];
end loop [ label ];

解释一下：

1、loop 定义一个无条件的循环（无限循环），直到由 exit 或 return 语句终止
2．可选的 label 可以由 exit 和 continue 语句使用，用于在嵌套循环中声明应该应用于哪一层循环
3．如果声明了 when，循环退出只有在 boolean-expression 为真的时候才发生,，否则控制会落到 exit 后面的语句上

示例如下所示：

postgres=# create or replace function loop_func1() returns void as $$
postgres$# declare
postgres$# n1 int := 1;
postgres$# n2 int := 1;
postgres$# n3 int := 0;
postgres$# begin
postgres$# loop
postgres$# n3 := n1 + n2;
postgres$# n1 := n2;
postgres$# n2 := n3;
postgres$# raise NOTICE 'n 的当前值为: %',n3;
postgres$# exit when n3 >= 100;
postgres$# -- return;
postgres$# end loop;
postgres$# end;
postgres$# $$ language plpgsql;
CREATE FUNCTION
postgres=# 
postgres=# select loop_func1();
NOTICE:  n 的当前值为: 2
NOTICE:  n 的当前值为: 3
NOTICE:  n 的当前值为: 5
NOTICE:  n 的当前值为: 8
NOTICE:  n 的当前值为: 13
NOTICE:  n 的当前值为: 21
NOTICE:  n 的当前值为: 34
NOTICE:  n 的当前值为: 55
NOTICE:  n 的当前值为: 89
NOTICE:  n 的当前值为: 144loop_func1 
------------(1 row)postgres=#

2、while循环语句

其语法格式如下：

while boolean-expression loopstatements;
end loop;

解释一下：

1、只要条件表达式(boolean-expression)为真，while 语句就会不停的在一系列语句上进行循环
2、条件是在每次进入循环体的时候检查的

示例如下：

postgres=# create or replace function while_func1() returns void as $$
postgres$# declare
postgres$# n1 int := 1;
postgres$# n2 int := 1;
postgres$# n3 int := 0;
postgres$# begin
postgres$# while n3 < 100 loop
postgres$# n3 := n1 + n2;
postgres$# n1 := n2;
postgres$# n2 := n3;
postgres$# raise NOTICE 'n 的当前值为: %',n3;
postgres$# end loop;
postgres$# end;
postgres$# $$ language plpgsql;
CREATE FUNCTION
postgres=# 
postgres=# select while_func1();
NOTICE:  n 的当前值为: 2
NOTICE:  n 的当前值为: 3
NOTICE:  n 的当前值为: 5
NOTICE:  n 的当前值为: 8
NOTICE:  n 的当前值为: 13
NOTICE:  n 的当前值为: 21
NOTICE:  n 的当前值为: 34
NOTICE:  n 的当前值为: 55
NOTICE:  n 的当前值为: 89
NOTICE:  n 的当前值为: 144while_func1 
-------------(1 row)postgres=#

3、for循环语句

其语法格式如下：

for name in [ reverse ] expression .. expression [ by expression ] loopstatements
end loop [ label ];

解释一下：

1、每循环一次，循环变量自动加1 （默认情况下）
2、若是使用关键字reverse，循环变量自动减1
3、跟在in [reverse] 后面的数字必须是从小到大的顺序，而且必须是整数，不能是变量或表达式。可以使用 exit 退出循环
4、by是步长

postgres=# create or replace function for_func1() returns void as $$
postgres$# begin
postgres$# for i in 1..10 loop
postgres$# raise NOTICE 'i 的当前值为: %',i;
postgres$# end loop;
postgres$# raise info '*******************';
postgres$# for i in reverse 10..1 loop
postgres$# raise NOTICE 'i 的当前值为: %',i;
postgres$# end loop;
postgres$# raise info '*******************';
postgres$# for i in reverse 10..1 by 2 loop
postgres$# raise NOTICE 'i 的当前值为: %',i;
postgres$# end loop;
postgres$# end;
postgres$# $$ language plpgsql;
CREATE FUNCTION
postgres=# 
postgres=# select for_func1();
NOTICE:  i 的当前值为: 1
NOTICE:  i 的当前值为: 2
NOTICE:  i 的当前值为: 3
NOTICE:  i 的当前值为: 4
NOTICE:  i 的当前值为: 5
NOTICE:  i 的当前值为: 6
NOTICE:  i 的当前值为: 7
NOTICE:  i 的当前值为: 8
NOTICE:  i 的当前值为: 9
NOTICE:  i 的当前值为: 10
INFO:  *******************
NOTICE:  i 的当前值为: 10
NOTICE:  i 的当前值为: 9
NOTICE:  i 的当前值为: 8
NOTICE:  i 的当前值为: 7
NOTICE:  i 的当前值为: 6
NOTICE:  i 的当前值为: 5
NOTICE:  i 的当前值为: 4
NOTICE:  i 的当前值为: 3
NOTICE:  i 的当前值为: 2
NOTICE:  i 的当前值为: 1
INFO:  *******************
NOTICE:  i 的当前值为: 10
NOTICE:  i 的当前值为: 8
NOTICE:  i 的当前值为: 6
NOTICE:  i 的当前值为: 4
NOTICE:  i 的当前值为: 2for_func1 
-----------(1 row)postgres=#

我们也可以用 for 循环来替代游标 （游标下面会做详细解释）

postgres=# select * from t1;id |   name   
----+----------1 | Postgres2 | Oracle3 | Mysql
(3 rows)postgres=# create or replace function loop_for_cursor() returns void as $$
postgres$# declare
postgres$# i record;
postgres$# begin
postgres$# for i in select id,name from t1 limit 2 loop
postgres$# raise notice '% %', i.id ,i.name;
postgres$# end loop;
postgres$# end;
postgres$# $$ language plpgsql;
CREATE FUNCTION
postgres=# 
postgres=# select * from loop_for_cursor();
NOTICE:  1 Postgres
NOTICE:  2 Oracleloop_for_cursor 
-----------------(1 row)postgres=# select loop_for_cursor();
NOTICE:  1 Postgres
NOTICE:  2 Oracleloop_for_cursor 
-----------------(1 row)postgres=#

4、continue语句和exit 语句

其语法格式如下：

	continue [ label ] [ when boolean-expression ];exit [ label ] [ when boolean-expression ];

解释一下：

1、continue 可以用于所有类型的循环，它并不仅仅限于无条件循环 不会跳出循环
2、exit 可以用于在所有的循环类型中，它并不仅仅限制于在无条件循环中使用 会跳出循环

下面来一下示例：

postgres=# create or replace function loop_func1() returns void as $$
declare
n1 int := 1;
n2 int := 1;
n3 int := 0;
begin
loop
n3 := n1 + n2;
n1 := n2;
n2 := n3;
continue when n3 = 55;
raise NOTICE 'n 的当前值为: %',n3;
if n3 > 100 then
exit;
end if;
end loop;
end;
$$ language plpgsql;
CREATE FUNCTION
postgres=# 
postgres=# select loop_func1();
NOTICE:  n 的当前值为: 2
NOTICE:  n 的当前值为: 3
NOTICE:  n 的当前值为: 5
NOTICE:  n 的当前值为: 8
NOTICE:  n 的当前值为: 13
NOTICE:  n 的当前值为: 21
NOTICE:  n 的当前值为: 34
NOTICE:  n 的当前值为: 89
NOTICE:  n 的当前值为: 144loop_func1 
------------(1 row)postgres=##---------------------------------------------------------------#postgres=# create or replace function loop_func1() returns void as $$
declare
n1 int := 1;
n2 int := 1;
n3 int := 0;
begin
loop
n3 := n1 + n2;
n1 := n2;
n2 := n3;
continue when n3 = 55;
raise NOTICE 'n 的当前值为: %',n3;
exit when n3 > 100;
end loop;
end;
$$ language plpgsql;
CREATE FUNCTION
postgres=# 
postgres=# select loop_func1();
NOTICE:  n 的当前值为: 2
NOTICE:  n 的当前值为: 3
NOTICE:  n 的当前值为: 5
NOTICE:  n 的当前值为: 8
NOTICE:  n 的当前值为: 13
NOTICE:  n 的当前值为: 21
NOTICE:  n 的当前值为: 34
NOTICE:  n 的当前值为: 89
NOTICE:  n 的当前值为: 144loop_func1 
------------(1 row)postgres=#

---

pl/pgsql函数的返回方式

---

在pl/pgsql函数中通常使用return命令来做 函数的结束 和 获取返回结果。通常返回方式有如下四种：

1、return
2、return next
3、return query
4、return query execute

1、return 命令

其语法格式如下：

returnreturn expression;

注：如果没有使用表达式，return 命令用于告诉这个函数已经完成执行了。如果返回标量类型，那么可以使用任何表达式。若是要返回一个复合(行)数值，那么必须写一个记录或者行变量的expression。

示例如下：

postgres=# create or replace function return_func1 (int) returns int
postgres-# as $$
postgres$# declare
postgres$# id int :=10;
postgres$# v_id alias for $1;
postgres$# -- 别名的另一种方式
postgres$# begin
postgres$# id := id * v_id;
postgres$# return id;
postgres$# end;
postgres$# $$ language plpgsql;
CREATE FUNCTION
postgres=# 
postgres=# select return_func1(10);return_func1 
--------------100
(1 row)postgres=# select * from return_func1(10);return_func1 
--------------100
(1 row)postgres=#

如果声明函数带输出参数，那么就只需要写无表达式的 return。那么输出参数变量的当前值将被返回，当然 这个 return 也可以不写！

postgres=# create or replace function return_func2 (in_col1 in int,in_col2 in text,out_col1 out int, out_col2 out text) as $$
postgres$# begin
postgres$# out_col1 := in_col1 + 1;
postgres$# out_col2 := in_col2 || '_result';
postgres$# return;
postgres$# end;
postgres$# $$ language plpgsql;
CREATE FUNCTION
postgres=# 
postgres=# select * from return_func2(1,'return_func2');out_col1 |      out_col2       
----------+---------------------2 | return_func2_result
(1 row)postgres=#

如果函数值类型是返回 void，那么一个 return 语句可以用于提前退出函数。

postgres=# create or replace function return_func1 (in_col1 int) returns void as $$
postgres$# begin
postgres$# if in_col1 > 0 then
postgres$# raise notice 'there is %',in_col1;
postgres$# else
postgres$# return;
postgres$# end if;
postgres$# end;
postgres$# $$ language plpgsql;
CREATE FUNCTION
postgres=# 
postgres=# select return_func1(1);
NOTICE:  there is 1return_func1 
--------------(1 row)postgres=# select return_func1(0);return_func1 
--------------(1 row)postgres=#

2、return next 命令

其语法格式如下：

return next expression;# 可以用于标量和复合数据类型。对于复合类型,将返回一个完整的结果"table"

示例如下：

postgres=# select * from t1;id |   name   
----+----------1 | Postgres2 | Oracle3 | Mysql
(3 rows)postgres=# create or replace function return_func2() returns setof t1 as $$
declare
var record;
t_sql text :='select * from t1';
begin
for var in execute t_sql loop
var.name:=var.name || 'database';
return next var;
end loop;
end;
$$ language plpgsql;
CREATE FUNCTION
postgres=# 
postgres=# select * from return_func2();id |       name       
----+------------------1 | Postgresdatabase2 | Oracledatabase3 | Mysqldatabase
(3 rows)postgres=# select return_func2();return_func2     
----------------------(1,Postgresdatabase)(2,Oracledatabase)(3,Mysqldatabase)
(3 rows)postgres=#

3、return query 命令

其语法格式如下：

return query query;# 可以用于将一条查询的结果追加到一个函数的结果集中

示例如下：

postgres=# select * from t1;id |   name   
----+----------1 | Postgres2 | Oracle3 | Mysql
(3 rows)postgres=# create or replace function return_func1() returns setof t1 as $$
postgres$# begin
postgres$# return query select * from t1 limit 2;
postgres$# end
postgres$# $$ language plpgsql;
CREATE FUNCTION
postgres=# 
postgres=# select * from return_func1();id |   name   
----+----------1 | Postgres2 | Oracle
(2 rows)postgres=# select return_func1();return_func1 
--------------(1,Postgres)(2,Oracle)
(2 rows)postgres=#

4、return query execute 命令

其语法格式如下：

return query execute command-string [ using expression [, ... ] ];# 可以用于将一条查询的结果追加到一个函数的结果集中

示例如下：

postgres=# select * from t1;id |   name   
----+----------1 | Postgres2 | Oracle3 | Mysql
(3 rows)postgres=# create or replace function return_func2() returns setof t1 as $$
begin
return query execute 'select * from t1 limit 2';
end
$$ language plpgsql;
CREATE FUNCTION
postgres=# 
postgres=# select return_func2();return_func2 
--------------(1,Postgres)(2,Oracle)
(2 rows)postgres=# select * from return_func2();id |   name   
----+----------1 | Postgres2 | Oracle
(2 rows)postgres=#

注：和 execute 类似，也是要防止 SQL 注入的。示例如下：

postgres=# select * from t1;id |   name   
----+----------1 | Postgres2 | Oracle3 | Mysql
(3 rows)postgres=# create or replace function return_func2(text) returns setof t1 as $$
postgres$# begin
postgres$# return query execute 'select * from '|| $1 || ' limit 2';
postgres$# end
postgres$# $$ language plpgsql;
CREATE FUNCTION
postgres=# 
postgres=# select * from return_func2('t1');id |   name   
----+----------1 | Postgres2 | Oracle
(2 rows)postgres=# select return_func2('t1');return_func2 
--------------(1,Postgres)(2,Oracle)
(2 rows)postgres=#

---

pl/pgsql函数返回结果集

---

在pl/pgsql函数中，一个函数可以有返回一个结果集 和 返回多个结果集的两种类型。

---

pl/pgsql函数返回单个结果集

---

1、使用复合类型

示例1如下：

postgres=# select * from t1;id |   name   
----+----------1 | Postgres2 | Oracle3 | Mysql
(3 rows)# 先创建一个复合类型
postgres=# create type mytype  as (tid int,tname text); 
CREATE TYPE
postgres=#
postgres=# create or replace function result_set_func1 (value t1) returns mytype as $$
postgres$# declare
postgres$# ret mytype;
postgres$# begin
postgres$# ret.tid = value.id * 10;
postgres$# ret.tname = value.name ||' database';
postgres$# return ret;
postgres$# end ;
postgres$# $$ language plpgsql;
CREATE FUNCTION
postgres=#
postgres=# select result_set_func1(t1.*) from t1;result_set_func1     
--------------------------(10,"Postgres database")(20,"Oracle database")(30,"Mysql database")
(3 rows)postgres=#
postgres=# select * from result_set_func1((1,'Postgres'));tid |       tname       
-----+-------------------10 | Postgres database
(1 row)postgres=#

示例2如下：

postgres=# create type mytype2 as (tid int,tname varchar(16)); 
CREATE TYPE
postgres=# select * from t1;id |   name   
----+----------1 | Postgres2 | Oracle3 | Mysql
(3 rows)postgres=# create or replace function result_set_func2() returns setof mytype2 as $$
begin
return query select * from t1;
end;
$$ language plpgsql;
CREATE FUNCTION
postgres=# select result_set_func2();result_set_func2 
------------------(1,Postgres)(2,Oracle)(3,Mysql)
(3 rows)postgres=# select * from result_set_func2();tid |  tname   
-----+----------1 | Postgres2 | Oracle3 | Mysql
(3 rows)postgres=#

当然也是可以直接使用创建表时创建的复合类型 t1，示例3如下：

postgres=# select * from t1;id |   name   
----+----------1 | Postgres2 | Oracle3 | Mysql
(3 rows)postgres=# create or replace function result_set_func2() returns setof t1 as $$
postgres$# declare
postgres$# ret record;
postgres$# begin
postgres$# for ret in execute 'select * from t1' loop
postgres$# return next ret;
postgres$# end loop;
postgres$# end;
postgres$# $$ language plpgsql;
CREATE FUNCTION
postgres=# 
postgres=# select * from result_set_func2();id |   name   
----+----------1 | Postgres2 | Oracle3 | Mysql
(3 rows)postgres=# select result_set_func2();result_set_func2 
------------------(1,Postgres)(2,Oracle)(3,Mysql)
(3 rows)postgres=#

2、使用输出参数

使用输出参数返回一行多列，示例如下：

postgres=# select * from t1;id |   name   
----+----------1 | Postgres2 | Oracle3 | Mysql
(3 rows)postgres=# create or replace function result_set_func1(in in_col1 integer,in in_col2 text,out out_col1 integer, out out_col2 text) as $$
postgres$# begin
postgres$# out_col1 := in_col1 * 2;
postgres$# out_col2 := in_col2 || ' database';
postgres$# end;
postgres$# $$ language plpgsql;
CREATE FUNCTION
postgres=# 
postgres=# select * from result_set_func1(1,'Postgres');out_col1 |     out_col2      
----------+-------------------2 | Postgres database
(1 row)postgres=#

通过使用输出参数来返回结果集，示例如下：

postgres=# select * from t1;id |   name   
----+----------1 | Postgres2 | Oracle3 | Mysql
(3 rows)postgres=# create or replace function result_set_func1(out o_id text,out o_name text) returns setof record as $$
postgres$# declare
postgres$# myrow record;
postgres$# begin
postgres$# for myrow in select * from t1 loop
postgres$# o_id := myrow.id+1;
postgres$# o_name := myrow.name;
postgres$# return next;
postgres$# end loop;
postgres$# end;
postgres$# $$
postgres-# language plpgsql;
CREATE FUNCTION
postgres=# 
postgres=# select * from result_set_func1();o_id |  o_name  
------+----------2    | Postgres3    | Oracle4    | Mysql
(3 rows)postgres=# select result_set_func1();result_set_func1 
------------------(2,Postgres)(3,Oracle)(4,Mysql)
(3 rows)postgres=## 同样支持对上面函数的结果进行过滤
postgres=#  select * from result_set_func1() where o_name like 'O%';o_id | o_name 
------+--------3    | Oracle
(1 row)postgres=#

3、使用表返回多行多列

示例如下：

postgres=# create or replace function result_set_func3(step int) returns table(col1 integer, col2 text)
as $$
begin
return query select n,n||'DataBase' from generate_series(1,5,step) as n;
end;
$$ language plpgsql;
CREATE FUNCTION
postgres=# 
postgres=# select result_set_func3(1);result_set_func3 
------------------(1,1DataBase)(2,2DataBase)(3,3DataBase)(4,4DataBase)(5,5DataBase)
(5 rows)postgres=# select * from result_set_func3(2);col1 |   col2    
------+-----------1 | 1DataBase3 | 3DataBase5 | 5DataBase
(3 rows)postgres=#

4、通过游标返回多行多列

示例如下：

postgres=# create or replace function result_set_func1(src varchar,delimiter varchar) returns setof text as $$
declareret text;mycus cursor for select regexp_replace(regexp_split_to_table(src,delimiter),e'(^\\s*)|(\\s*$)','','g');
beginopen mycus;loopfetch mycus into ret;exit when not found;if ret is null thencontinue;end if;raise notice 'character is: %',ret;return next ret;end loop;
end;
$$ language plpgsql;
CREATE FUNCTION
postgres=# 
postgres=# select result_set_func1('Postgres#Oracle#Mysql','#');
NOTICE:  character is: Postgres
NOTICE:  character is: Oracle
NOTICE:  character is: Mysqlresult_set_func1 
------------------PostgresOracleMysql
(3 rows)postgres=#

5、通过创建临时表返回结果集

示例如下：

postgres=# select * from t1;id |   name   
----+----------1 | Postgres2 | Oracle3 | Mysql
(3 rows)postgres=# \dList of relationsSchema | Name | Type  | Owner 
--------+------+-------+-------public | t1   | table | uxdb
(1 row)postgres=# create or replace function table_tmp() returns setof t1 as $$
begin
create temporary table t1_tmp as select * from t1 limit 2;
return query select * from t1_tmp;
drop table t1_tmp;
end ;
$$ language plpgsql;
CREATE FUNCTION
postgres=# select * from table_tmp();id |   name   
----+----------1 | Postgres2 | Oracle
(2 rows)postgres=# \dList of relationsSchema | Name | Type  | Owner 
--------+------+-------+-------public | t1   | table | uxdb
(1 row)postgres=#

---

pl/pgsql函数返回多个结果集

---

在 SQLServer 和 MySQL 中，在函数里面可以有多个 select，即返回多个结果集。而在PostgreSQL 中是可以通过游标和拆分的方式来实现函数返回多个结果集。

1、使用游标

示例如下：

postgres=# select * from t1;id |   name   
----+----------1 | Postgres2 | Oracle3 | Mysql1 | Postgres2 | Oracle3 | Mysql1 | Postgres
(7 rows)postgres=# create table t2 as select * from t1;
SELECT 7
postgres=# create or replace function more_result2(inout refcursor,inout refcursor) returns record as $$
postgres$# begin
postgres$# open $1 for select * from t1 limit 5;
postgres$# open $2 for select * from t2;
postgres$# end;
postgres$# $$ language plpgsql;
CREATE FUNCTION
postgres=# 
postgres=#  select * from more_result2('mycus1','mycus2');column1 | column2 
---------+---------mycus1  | mycus2
(1 row)postgres=#  fetch all in "mycus1";
2020-10-01 19:40:38.834 CST [1682] ERROR:  cursor "mycus1" does not exist
2020-10-01 19:40:38.834 CST [1682] STATEMENT:  fetch all in "mycus1";
ERROR:  cursor "mycus1" does not exist
postgres=#  fetch all in "mycus2";
2020-10-01 19:40:49.122 CST [1682] ERROR:  cursor "mycus2" does not exist
2020-10-01 19:40:49.122 CST [1682] STATEMENT:  fetch all in "mycus2";
ERROR:  cursor "mycus2" does not exist
postgres=# begin;
BEGIN
postgres=*# select * from more_result2('mycus1','mycus2');column1 | column2 
---------+---------mycus1  | mycus2
(1 row)postgres=*#  fetch all in "mycus1";id |   name   
----+----------1 | Postgres2 | Oracle3 | Mysql1 | Postgres2 | Oracle
(5 rows)postgres=*#  fetch all in "mycus2";id |   name   
----+----------1 | Postgres2 | Oracle3 | Mysql1 | Postgres2 | Oracle3 | Mysql1 | Postgres
(7 rows)postgres=*# rollback;
ROLLBACK
postgres=#

2、使用临时表

示例如下：

postgres=# select * from t1;id |   name   
----+----------1 | Postgres2 | Oracle3 | Mysql1 | Postgres2 | Oracle3 | Mysql1 | Postgres
(7 rows)postgres=# create or replace function get_num() returns setof record as $$
postgres$# declare
postgres$# myret record;
postgres$# begin
postgres$# create temp table mytmp_t(name text,num int);
postgres$# insert into mytmp_t(name,num) SELECT name,count(*) as num FROM t1 WHERE name = 'Oracle' GROUP BY name;
postgres$# insert into mytmp_t(name,num) SELECT name,count(*) as num FROM t1 WHERE name = 'Postgres' GROUP BY name;
postgres$# insert into mytmp_t(name,num) SELECT name,count(*) as num FROM t1 WHERE name = 'Mysql' GROUP BY name;
postgres$# 
postgres$# for myret in select * from mytmp_t loop
postgres$# postgres$# return next myret;
postgres$# end loop;
postgres$# drop table mytmp_t;
postgres$# end;
postgres$# $$ language plpgsql;
CREATE FUNCTION
postgres=# 
postgres=# select * from get_num() as (myname text,myid int);myname  | myid 
----------+------Oracle   |    2Postgres |    3Mysql    |    2
(3 rows)postgres=#

3、拆分成多个单个结果集

通过拆分逻辑成多个单个结果集，不过就需要多个函数了！