虽然真的很鸡肋,并且性能不杂滴,但聊胜于无吧.
drop function if exists exe_dynamic_sql(bigint);
drop function if exists exe_dynamic_count(bigint);
--返回记录集
create or replace function exe_dynamic_sql(ival bigint)
returns table(objectid bigint,name varchar(128))
as $$
declare
begin
return query execute 'select objectid,name from dictionarys where parentid=$1 order by parentid,sort' using $1;
end;
$$ language plpgsql;
--赋值给变量
create or replace function exe_dynamic_count(ival bigint)
returns bigint
as $$
declare
v_count bigint;
begin
execute 'select count(*) from dictionarys where parentid=$1' using $1 into v_count;
return v_count;
end;
$$ language plpgsql;
--测试
select * from exe_dynamic_sql(26);
select exe_dynamic_count(26);
要点:
- returns table(objectid bigint,name varchar(128)),定义返回的字段和类型
- using $1执行时使用过程参数;