Postgresql自定义函数详解
程序员文章站
2022-03-13 22:30:43
postgresql函数也称为postgresql存储过程。 postgresql函数或存储过程是存储在数据库服务器上并可以使用sql界面调用的一组sql和过程语句(声明,分配,循环,控制流程等)。语...
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,'*'),(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,"*") (4,shainghai) (4 行记录)
也可以用类似查询表的方式。
mydb=# select * from getcity(); cityid | cityname --------+----------- 1 | beijing 2 | newyork 3 | * 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';
执行函数。
mydb=# select * from getrows('city') as city(id int, name varchar(20)); id | name ----+----------- 1 | beijing 2 | newyork 3 | * 4 | shainghai (4 行记录)
以上为个人经验,希望能给大家一个参考,也希望大家多多支持。如有错误或未考虑完全的地方,望不吝赐教。