pg11函数存储过程调用示例

pg11函数存储过程调用总结

测试数据：

create table test(id int,name varchar,age int);
insert into test values(1,'hh',30);

函数调用方式：

对于函数调用返回值为refcursor类型的调用方式，一行作为一个语句执行

begin;
select * from function(); --调用函数名
fetch  all  in "<游标编码号>";  --根据select * from function()查询结果得出游标编码号,再通过fetch显示结果集
end;

存储过程调用方式：

DO $$
DECLARE proc_param int := 5; --参数
BEGIN
  CALL proc_name(proc_param);--调用
END
$$;

一、函数

函数无OUT参数，返回值跟returns 类型保持一致；

如果函数带OUT参数返回,分两种情况：只有一个OUT参数，函数返回值类型与OUT参数保持一致；有多个OUT参数，返回值类型为RECORD类型；此时returns data_type可有可无，存在时，类型则与要求的保持一致。

函数定义:返回RECORD类型：

create or replace function test_fun(v1 int, v2 out varchar,v3 out int) as $$  --默认返回值为record类型
declare 
p1 test;
p2 test[];
p3 refcursor;
v_name varchar;
v_age int;
begin
truncate table test;
insert into test values(1,'cs',40);
select name,age into v2,v3 from test where id = v1;
INSERT  INTO test (id,name,age)VALUES(2,'tom',12) RETURNING * INTO p1;
raise notice 'INSERT-RETURNING:%',p1;
INSERT  INTO test (id,name,age)VALUES(3,'hhdb',22) RETURNING name,age INTO v_name,v_age;
raise notice 'INSERT-RETURNING:%,%',v_name,v_age;
UPDATE test SET name = 'John'  WHERE id = 1 RETURNING * INTO p1;--超过一行会报错
raise notice 'UPDATE-RETURNING:%',p1;
DELETE FROM test WHERE id= 1 RETURNING * INTO p1;  --数组超过一行也会报错
raise notice 'DELETE-RETURNING:%',p1;
--DELETE FROM test WHERE id!= 1 RETURNING * INTO p2;  --数组超过一行也会报错
--DELETE FROM test WHERE id!= 1 RETURNING * INTO p3;  --数组超过一行也会报错
end
$$ language plpgsql;

函数调用：

select   (test_fun(1)).v2;   --默认返回值为record类型
select   (test_fun(1)).v3;  --默认返回值为record类型
select v2,v3 from test_fun(1);  --默认返回值为record类型
select * from test_fun(1);   --默认返回值为record类型

函数定义：与OUT参数返回值类型一致：

create or replace function test_fun(v1 int, v2 out varchar) as $$     --默认返回值为varchar类型,返回值类型可写可不写
begin
select name,age into v2 from test where id = v1;
end
$$ language plpgsql;

函数调用：

select   (test_fun(1)).v2;  ---非法调用，函数默认返回为varchar类型
select v2 from test_fun(1);
select * from test_fun(1)

函数定义：与OUT参数返回值类型一致：

create or replace function test_fun(v1 int, v2 out refcursor) as $$    --默认返回值为refcursor类型,返回值类型可写可不写
begin
open v2 for select name,age from test where id = v1;
end
$$ language plpgsql;

函数调用：

begin 
select * from test_fun(1);
fetch  all  in "<unnamed portal 2>";
end

函数定义：返回值类型为RECORD,里面包括游标复杂类型

create or replace function test_fun(v1 int,v2 out varchar, v3 out refcursor) as $$  --默认返回值为record类型,返回值类型可写可不写
begin
--open v3 for select name into v2   from test where id = v1; --此情况创建正常,调用出错,转成以下两个语句，同时into 多个变量PG不支持，oracle虽然支持，但是运行结果显示并没有将值INTO到指定的变量中
open v3 for select *  from test where id = v1;
select name into v2  from test where id = v1;
end
$$ language plpgsql;

函数调用：

begin;
select * from test_fun(1);
fetch  all  in "<unnamed portal 4>";
end;

二、存储过程

存储过程定义：

CREATE PROCEDURE triple(INOUT x int)
LANGUAGE plpgsql
AS $$
BEGIN
    x := x * 3;
END;
$$;

存储过程调用：

DO $$
DECLARE myvar int := 5;
BEGIN
  CALL triple(myvar);
  RAISE NOTICE 'myvar = %', myvar;  -- prints 15
END
$$;