Re: execute block like Firebird does

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