PostgreSQL執行動態SQL回傳多個結果集
- 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;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/216161.html
標籤：其他
上一篇：網路程式設計實驗