新乡网站建设-中国互联,一起作做业网站,苏州浒关做网站,创作平台有哪些文章目录 标量示例复合示例有返回值函数返回voidRETURN NEXT ,RETURN QUERYRETURN EXECUTEIF THEN END IFFOREACH,LOOPSLICE #xff08;1#xff09;如果函数返回一个标量类型#xff0c;表达式结果将自动转行成函数的返回类型。但要返回一个复合#xff08;行#xff09… 文章目录 标量示例复合示例有返回值函数返回voidRETURN NEXT ,RETURN QUERYRETURN EXECUTEIF THEN END IFFOREACH,LOOPSLICE 1如果函数返回一个标量类型表达式结果将自动转行成函数的返回类型。但要返回一个复合行值必须写一个所需列集合的表达式。 标量示例
create or replace function fn_scalar() returns numeric as
$$
declare
begin return (34*2)-(2*2-1);
end
$$
language plpgsqlselect * 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 plpgsqlselect * 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 plpgsqlselect * from fn_out_return ()函数返回void
如果声明函数返回voidRETURN可以用来提前结果函数但函数最后不要写RETURN
create or replace function fn_void_return() returns void as
$$
declare
begin raise notice 执行第一行....;raise notice 执行第二行....;
return;raise notice 执行第三行....;
end$$
language plpgsqlselect * from fn_void_return() 执行第一行… 执行第二行… RETURN NEXT ,RETURN QUERY 当函数被声明为返回returns setof sometype,规则和直接return sometype有所不同。这种情况下返回的个体项被RETURN NEXT或者RETURN QUERY 命令序列指定并接着会用一个不带参数的RETURN命令来指示这个函数已经完成执行。 1RETURN NEXT可以返回标量和复合类型对于复合类型将返回一个完整的结果“表”结果集。 create or replace function fn_return_nexts() returns setof product as
$$
declare
r product%rowtype;
begin for r in select * from productloopraise notice name is :%,r.name;return next r;end loop;
return;
end$$
language plpgsqlselect * 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 2RETURN QUERY 将执行一个查询的结果追加到一个函数结果中。 create or replace function fn_return_query() returns setof product as
$$
declare
r record;
begin return query(select * from product);end$$
language plpgsqlselect * 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 plpgsqlselect * from fn_query_execute(s)sqlselect * 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 $1loop raise notice 输出value%,x; end loop;end$$
language plpgsqlselect * from fn_foreach(array[[1,2,3],[4,5,6]]); 输出value1 输出value2 输出value3 输出value4 输出value5 输出value6 select * from fn_foreach(array[1,2,3]) 输出value1 输出value2 输出value3 create or replace function fn_foreach(int[]) returns void as
$$
declare
x int[];
begin foreach x slice 2 in array $1loop raise notice 输出value%,x; end loop;end$$
language plpgsqlselect * from fn_foreach(array[[1,2,3],[4,5,6]]);
输出value{{1,2,3},{4,5,6}}
