PostgreSQL执行动态SQL返回多个结果集

PostgreSQL执行动态SQL返回多个结果集

废话少说直接上代码

创建存储

CREATE OR REPLACE FUNCTION test(ref1 refcursor, ref2 refcursor,aa varchar)

RETURNS SETOF refcursor AS $BODY$
DECLARE
sqlString varchar(4000) = ‘’; --查询sql
countSqlString varchar(4000) = ‘’; --统计sql

BEGIN

– IF(aa= ‘*’) THEN
– Begin
– SqlString := ‘’; – 拼接条件
– End;
– ELSE
– Begin
– SqlString := ‘’;
– End;
– END IF;

– IF ends_with IS NOT NULL THEN
– sql := sql || ‘’; --拼接sql
– END IF;

SqlString := ‘SELECT * FROM act_ge_property WHERE value_=’’’||aa||’’’ limit 10 OFFSET 0’;

countSqlString := ‘SELECT COUNT(1) FROM act_ge_property WHERE value_=’’’||aa||’’’ limit 10 OFFSET 0’;
– EXECUTE(SqlString);
– EXECUTE(countSqlString);
– RETURN QUERY EXECUTE SqlString;
– 打印SQL
– raise notice ‘sql=>%’,SqlString;

open $1 FOR EXECUTE(SqlString);
RETURN NEXT $1;

open $2 FOR EXECUTE(countSqlString);
RETURN NEXT $2;

RETURN;
END;
$BODY$LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000

执行查询

BEGIN;
SELECT test(‘a’,‘b’,‘1’);
FETCH ALL in “a”;
FETCH ALL in “b”;
COMMIT;