PegoraroF10 wrote:
> Another approach to solve my problem would be a function that receives a
> dynamic SQL, runs it and returns a XML or JSON and on client side I convert
> that XML back to a recordset. Is that possible ?
Yet another tool that can be handy to transfer polymorphic
results is a cursor through the plpgsql REFCURSORs:
https://www.postgresql.org/docs/10/static/plpgsql-cursors.html
Here's an actual example with a DO block :
BEGIN;
DO $$
DECLARE
c1 refcursor := 'cursor1';
c2 refcursor := 'cursor2';
BEGIN
OPEN c1 FOR select 'result #1 column 1', 'result #1 column 2';
OPEN c2 FOR select 'result #2 column 1', 'result #2 column 2';
END;
$$ LANGUAGE plpgsql;
FETCH cursor1;
FETCH cursor2;
COMMIT;
The interface is a bit weird because the value of the refcursor variable
is the name of the underlying SQL cursor object. The name can
also be auto-generated by postgres; the above code uses fixed
names instead. Anyway that cursor, once instanciated in the
DO block, can be fetched from with FETCH statements initiated
client-side or by other server-side code.
The above code will retrieve two independant resultsets:
postgres=# FETCH cursor1;
?column? | ?column?
--------------------+--------------------
result #1 column 1 | result #1 column 2
(1 row)
postgres=# FETCH cursor2;
?column? | ?column?
--------------------+--------------------
result #2 column 1 | result #2 column 2
(1 row)
These cursors disappear at transaction end, or they can be explicitly
closed with CLOSE statements.
Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite