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