Best way to use a postgresql sproc (returning ref cursor) with PHP ?

Question

Hi.

I'm using stored procs and call them from PHP (I use the same procedures in a Java app too).

Right now I do it this way (which works):

if(!($result = pg_query($connection, 'BEGIN; SELECT '.$query.'; FETCH ALL IN '.self::$cursor.';'))) return NULL;

where $query is something like "CALL create_account('foo', 'bar', 'etc')" and $cursor is just "ref_cursor" since that is the cursor name (and yes, I know it seems like a hack...).

I know about the benefits of procedures (and prepared statements), and I wonder if there's any point in executing the above. A procedure is pre-compiled as far as I know, but the query above is not. So am I just fooling my self in believing that I would get some performance gain in this? I'm kind of bound to my procedures because I have an auto generator in Java that writes them for me. Is it better to use PDO in this case? I've searched on the net for something on pgsql ref cursors + pdo, but I didn't find much. I know I could just use PDO prepared statements but that would not coop with my procedures.

-Yngve

Answer 1

The 'better way' would be using pg_query_params, but you send only 1 query/statement at a time:

 pg_query_params('SELECT procedure_name($1, $2);'.array('foo','bar'));