Can you return multiple result sets using PDO and PostgreSQL?

Question

I would like to group several queries into one function that lives in PostgreSQL. The function will be requested using PDO.

Function:

CREATE OR REPLACE FUNCTION "test_multipe_refcursor"() 
RETURNS SETOF refcursor AS $BODY$

DECLARE
    parentRC refcursor;
    childRC refcursor;

BEGIN

open parentRC FOR
SELECT * FROM parent;
RETURN NEXT parentRC;

open childRC FOR
SELECT * FROM child;
RETURN NEXT childRC;

RETURN;

END;$BODY$
LANGUAGE 'plpgsql' VOLATILE;

ALTER FUNCTION "test_multipe_refcursor"() OWNER TO postgres;

Here is the PHP code. A “database” as a singleton class that sets the usual properties of a connection is nothing special.

  $database = Database::load();
  $sql = "select * from test_multipe_refcursor();";
  $p = $database->query($sql);

  $i = 1;
  do
  {
     $this->set('set' . $i, $p->fetchAll(PDO::FETCH_ASSOC));
     $i++;
  } while ($p->nextRowset());

  $p->closeCursor();

And the result.

 PDOException: SQLSTATE[IM001]: Driver does not support this function: driver does not support multiple rowsets in xxxx.php on line 32

This, apparently, indicates that it is not supported, but again, I can not find a list that defines exactly what is.

Has anyone been able to get this to work?

References:

• http://www.sitepoint.com/forums/showthread.php?p=3040612#post3040612
• PostgreSQL function returns multiple result sets
• http://ca.php.net/manual/en/pdostatement.nextrowset.php

Answer 1

- PostgreSQL todo list, , , 8.4. setof refcursors, , , , - ​​ refcursors. , refcursors, , - , , PDO API .

? .

Answer 2

PostgreSQL, , , . , , () :

CREATE FUNCTION myfunc(refcursor, refcursor) RETURNS SETOF refcursor AS $$
BEGIN
    OPEN $1 FOR SELECT * FROM table_1;
    RETURN NEXT $1;
    OPEN $2 FOR SELECT * FROM table_2;
    RETURN NEXT $2;
END;
$$ LANGUAGE plpgsql;

-- need to be in a transaction to use cursors.
BEGIN;

SELECT * FROM myfunc('a', 'b');

FETCH ALL FROM a;
FETCH ALL FROM b;
COMMIT;

PostgreSQL 8.4, 8.1 (, ). , , (.. , ).