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:

Here's an actual example with a DO block :


  DO $$
     c1 refcursor := 'cursor1';
     c2 refcursor := 'cursor2';
     OPEN c1 FOR select 'result #1 column 1', 'result #1 column 2';
     OPEN c2 FOR select 'result #2 column 1', 'result #2 column 2';
  $$ LANGUAGE plpgsql;

  FETCH cursor1;
  FETCH cursor2;


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:
Twitter: @DanielVerite

Reply via email to