Can you return multiple result sets using PDO and PostgreSQL?

Question

I would like to group multiple queries into a single function that lives in PostgreSQL. The function will be queried using PDO.

The function is:

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's the PHP code. "Database" as a singleton class that sets up the usual connection properties, 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 would seem to indicate that it's not supported, but then again, I cannot find a list defining exactly what is.

Has anyone managed to get this working?

References:

• http://www.sitepoint.com/forums/showthread.php?p=3040612#post3040612
• http://stackoverflow.com/questions/756689/postgresql-functions-returning-multiple-result-sets
• http://ca.php.net/manual/en/pdostatement.nextrowset.php

Answer 1

Support for returning multiple resultsets is still on the PostgreSQL todo list and it will definitely not hit 8.4. As for the setof refcursors method, What you are trying to do doesn't work because the function isn't returning multiple rowsets - it is returning one rowset of refcursors. I'm not sure if using refcursors client side works, but I don't find it likely, even if the client-server protocol supports it, it is unlikely that PDO has an API for that.

But why are you trying to return multiple resultsets in one query? You can always do the queries separately.

Answer 2

Near the bottom of this PostgreSQL doc page, there is a section describing how you can pass back one or more cursors from a function. Basically, you get the caller to specify the name of the cursor(s) as parameters:

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;

The page is for PostgreSQL 8.4, but this documentation snippet is present at least as far back as 8.1 (the version I'm running). As the comment says, you need to be inside a transaction to use cursors, as they are implicitly closed at the end of each transaction (i.e. at the end of every statement if autocommit mode is on).