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 $$;