文章目录
标量示例复合示例有返回值函数返回voidRETURN NEXT ,RETURN QUERYRETURN EXECUTEIF THEN END IFFOREACH,LOOPSLICE
（1）如果函数返回一个标量类型，表达式结果将自动转行成函数的返回类型。但要返回一个复合（行）值，必须写一个所需列集合的表达式。
标量示例
create or replace function fn_scalar() returns numeric as
$$
declare
begin
return (3+4*2)-(2*2-1);
end
$$
language plpgsql
select * from fn_scalar();
select * from fn_scalar() as cnt
复合示例
create or replace function fn_scalars() returns record as
$$
declare
begin
return (1,2,'hello world'::text);
end
$$
language plpgsql
select * from fn_scalars() as (no1 int,no2 int,msg text);
（2）如果声明带输出参数的函数，只需要写不带表达式的RETURN，输出参数变量的当前值被返回
有返回值
create or replace function fn_out_return(out rcd text) returns text as
$$
declare
begin
select name into rcd from product where id = 1 ;
end
$$
language plpgsql
select * from fn_out_return ()
函数返回void
如果声明函数返回void，RETURN可以用来提前结果函数，但函数最后不要写RETURN
create or replace function fn_void_return() returns void as
$$
declare
begin
raise notice '执行第一行....';
raise notice '执行第二行....';
return;
raise notice '执行第三行....';
end
$$
language plpgsql
select * from fn_void_return()
执行第一行… 执行第二行…
RETURN NEXT ,RETURN QUERY
当函数被声明为返回returns setof sometype,规则和直接return sometype有所不同。这种情况下，返回的个体项被RETURN NEXT或者RETURN QUERY 命令序列指定，并接着会用一个不带参数的RETURN命令来指示这个函数已经完成执行。
（1）RETURN NEXT可以返回标量和复合类型，对于复合类型，将返回一个完整的结果“表”（结果集）。
create or replace function fn_return_nexts() returns setof product as
$$
declare
r product%rowtype;
begin
for r in select * from product
loop
raise notice 'name is :%',r.name;
return next r;
end loop;
return;
end
$$
language plpgsql
select * from fn_return_nexts()
name is :diam name is :vestibulum aliquet name is :lacinia erat name is :scelerisque quam turpis name is :justo lacinia name is :ultrices mattis odio name is :hendrerit name is :in hac habitasse name is :orci eget orci name is :pellentesque name is :sit amet nunc name is :sed vestibulum name is :turpis eget name is :cursus vestibulum name is :orci nullam name is :est quam pharetra name is :posuere name is :ligula name is :convallis name is :nulla elit ac
（2）RETURN QUERY 将执行一个查询的结果追加到一个函数结果中。
create or replace function fn_return_query() returns setof product as
$$
declare
r record;
begin
return query(select * from product);
end
$$
language plpgsql
select * from fn_return_query()
返回结果同上所示
RETURN EXECUTE
create or replace function fn_query_execute(v_name varchar) returns setof product as
$$
declare
_sql text;
begin
_sql := 'select * from product where name like '''|| v_name || '%'';';
raise notice 'sql====%',_sql;
return query execute _sql;
end
$$
language plpgsql
select * from fn_query_execute('s')
sql====select * from product where name like ‘s%’;
IF THEN END IF
create or replace function fn_if_else(uid int) returns text as
$$
declare
v_value text;
begin
if uid = 1 then
v_value='参数值为1';
else
v_value='参数值不为1';
end if;
return v_value;
end
$$
language plpgsql
FOREACH,LOOP
https://blog.csdn.net/qq_39727113/article/details/115756087
SLICE
create or replace function fn_foreach(int[]) returns void as
$$
declare
x int;
begin
foreach x slice 0 in array $1
loop
raise notice '输出value=%',x;
end loop;
end
$$
language plpgsql
select * from fn_foreach(array[[1,2,3],[4,5,6]]); 输出value=1 输出value=2 输出value=3 输出value=4 输出value=5 输出value=6
select * from fn_foreach(array[1,2,3]) 输出value=1 输出value=2 输出value=3
create or replace function fn_foreach(int[]) returns void as
$$
declare
x int[];
begin
foreach x slice 2 in array $1
loop
raise notice '输出value=%',x;
end loop;
end
$$
language plpgsql
select * from fn_foreach(array[[1,2,3],[4,5,6]]);
输出value={{1,2,3},{4,5,6}}
相关阅读
您阅读本篇文章共花了：
发表评论