Php – Can you return multiple result sets using PDO and PostgreSQL

pdophppostgresql

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:

Best Solution

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.

Related Question