A while back I discussed how to use the new sqlsrv extension for php to retrieve multiple result sets at a time from stored proceedures. This was indeed helpful when you need to retrieve related data and avoid multiple calls to stored proceedures. The down side is this was using a rather expensive (but good) database engine. Also, until recently, there was absolutely no support for the sqlsrv php extension on unix based servers. How can the open source world tap into such functionality?

PostgreSQL has the ability to return multiple cursors to result sets. Using this we can achieve the same effect with php with a little magic. Lets take a look at a simple function which returns three queries by way of refcursor type. Also note that I said “function”, not “procedure”, but never fear, 6 of one, half dozen of the other as far as this topic goes:

CREATE OR REPLACE FUNCTION test_get_users(userID integer) RETURNS SETOF refcursor AS $$

	DECLARE 
		ref1 refcursor;
		ref2 refcursor;
		ref3 refcursor;
	BEGIN

	OPEN ref1 FOR 
		SELECT id, name, email FROM users;
	RETURN NEXT ref1;

	OPEN ref2 FOR 
		SELECT id FROM users WHERE is_active=1;
	RETURN next ref2;

	OPEN ref3 FOR 
		SELECT * FROM users WHERE id = userID;
	RETURN next ref3;

	RETURN;
	
END;
$$ LANGUAGE plpgsql;

So that is pretty simple, we run 3 select queries, and return the refcursor for those. Now the tricky part will be how to read each of these queries on the php end. The values returned from calling this function will resemble the image presented here. These merely represent references to the cursors to each query run, so we will have to use some pg magic to pull those result sets in. It boils down to we need to make one call to the function, then a separate call per result set to bring in those results (note the query has run at this point, its basically reading in the data, which should be quick). Lets look at the SQL which would achieve this:

BEGIN;
SELECT test_get_users(2);
FETCH ALL IN "<unnamed portal 1>";
FETCH ALL IN "<unnamed portal 2>";
FETCH ALL IN "<unnamed portal 3>";
END;

Now it should make a little more sense what has to happen. We call a fetch for each result set to return. So now we just need to process this with PHP:

$conn = new PDO("pgsql:host=localhost;port=5432;dbname=testing", "username", "password");

$userID = 2;

// begin transaction, this is all one process
$conn->beginTransaction();

	// call the function
	$stmt = $conn->prepare("select test_get_users(:userID)");
	$stmt->bindParam('userID', $userID, PDO::PARAM_INT);
	$stmt->execute();
	$cursors = $stmt->fetchAll();
	$stmt->closeCursor();

	// get each result set
	$results = array();
	foreach($cursors as $k=>$v){
		$stmt = $conn->query('FETCH ALL IN "'. $v[0] .'";');
		$results[$k] = $stmt->fetchAll();
		$stmt->closeCursor();
	}
$conn->commit();
unset($stmt);

echo '<pre>';
print_r($results);echo "\n"; // all record sets
echo '</pre>';

Now first off, we call the select test_get_users(), which returns a list of refcursor names. So with this we know how many queries were returned. Just loop through this list and fetch each result set. It is essentially the same process used from the Sql Server example, except the nextRowset() is not yet implemented for the pdo pgsql extension, so we just manually call the fetch. No Biggie. You would then receive something similar to the following:

Array
(
	[0] => Array
		(
			[0] => Array
				(
					[id] => 2
					[0] => 2
					[name] => John Doe
					[1] => John Doe
					[email] => test2@test.com
					[2] => test2@test.com
				)

			[1] => Array
				(
					[id] => 3
					[0] => 3
					[name] => Jane Doe
					[1] => Jane Doe
					[email] => test3@test.com
					[2] => test3@test.com
				)

			......

		)

	[1] => Array
		(
			[0] => Array
				(
					[id] => 3
					[0] => 3
				)

			[1] => Array
				(
					[id] => 1
					[0] => 1
				)

		)

	[2] => Array
		(
			[0] => Array
				(
					[id] => 2
					[0] => 2
					[name] => John Doe
					[1] => John Doe
					[email] => test2@test.com
					[2] => test2@test.com
					[password] => zxcvxcvczxcv
					[3] => zxcvxcvczxcv
					[is_active] => 0
					[4] => 0
				)

		)

)

If you’re still feeling frisky at this point, there are all sorts of ways to “fetch” the result sets, just take a look at the docs on cursors. The more I use PostgreSQL the more I am becoming a fan. It’s power and features go far beyond my skill set, but it feels good always knowing you have the best tool at hand for whatever task you have ahead of you. As always feel free to chime in and leave any knowledge you may have, especially on Postgres!

Related posts:

  1. Making Sense of Stored Procedures with PHP, PDO, and Sqlsrv
  2. Thought: Is else return really necessary?
  3. CakePHP, PostgreSQL, and Regex
  4. JsonSQL Ported to PHP
  5. PHP MSSQL Charset Issues, Resolved