Postgresql自定义函数详解

postgresql函数也称为postgresql存储过程。 postgresql函数或存储过程是存储在数据库服务器上并可以使用sql界面调用的一组sql和过程语句(声明，分配，循环，控制流程等)。

语法：

create [or replace] function function_name (arguments)  
returns return_datatype as $variable_name$ 
 declare 
  declaration; 
  [...] 
 begin 
  < function_body > 
  [...] 
  return { variable_name | value } 
 end; language plpgsql;

参数说明

function_name:指定函数的名称。

[or replace]:是可选的，它允许修改/替换现有函数。

declare:定义参数(参数名写在前面类型写在后面)。

begin~end: 在中间写方法主体。

return:指定要从函数返回的数据类型(它可以是基础，复合或域类型，或者也可以引用表列的类型)。

language:它指定实现该函数的语言的名称。

下面我将创建一个简单的函数：

create or replace function test(id uuid)
 returns integer
 language plpgsql
as
$$
declare
 count integer;
begin
 count = (select
       json_array_length(a.json::json -> 'features')
      from "json" a
      where a.uid = id);
 return count;
end;
 
$$

该函数的功能是用来返回json中数组的长度的

定义好该函数后，我们可以像调用其他的函数一样调用它

select test(id)

返回数组长度(integer)

补充：postgresql中自定义函数（function）返回数据集

1.准备数据

建表

 create table city (
 cityid int, 
 cityname varchar(20)
 );

插入数据

insert into city values(1,’beijing’),(2,’newyork’),(3,’hong kong’),(4,’shainghai’);

2. 函数实例

2.1 setof 表名

创建函数getcity()，它返回city表中所有的数据，返回值声明为 setof 表名。

create or replace function getcity() returns setof city as 
$$
begin
return query select * from city;
end;
$$
language plpgsql;

执行函数。

mydb=# select getcity();
   getcity
-----------------
 (1,beijing)
 (2,newyork)
 (3,"hong kong")
 (4,shainghai)
(4 行记录)

也可以用类似查询表的方式。

mydb=# select * from getcity();
 cityid | cityname
--------+-----------
   1 | beijing
   2 | newyork
   3 | hong kong
   4 | shainghai
(4 行记录)

postgresql还支持对函数执行结果进行条件判断并过滤。

mydb=# select * from getcity() where cityid > 3;
 cityid | cityname
--------+-----------
   4 | shainghai
(1 行记录)

2.2 setof record

为了使函数更加通用，以解决动态返回数据集的问题。创建函数getrows(text)，将表名city作为函数参数，它返回表中所有的数据，返回值声明为 setof record。

create or replace function getrows(text) returns setof record as
$$
declare
rec record;
begin
for rec in execute 'select * from ' || $1 loop
return next rec;
end loop;
return;
end
$$
language 'plpgsql';