Re: Re: pgSQL Functions with results set to php?

Richard Lynch wrote:

List, I'm creating a PHP/pgSQL web site...I need to execute queries with a cursor and get their result set into my PHP script. How can I make a pgSQL procedure with logic that also returns a result set? I've searched and searched but cannot find the answer. Does anyone got it?

Any old select will return a result set that works with a query... But if you need your PostgreSQL FUNCTION to return a result set, I *think* you need to use: 'set of XXX' for the 'rettype' (return type) and I *THINK* you can figure out what to use for XXX if you start digging to find the name/oid of the "complex type" that represents a row in your table here: http://www.postgresql.org/idocs/index.php?catalog-pg-type.html I've never done this, I just did a little digging right now, and this is where I would keep digging if I was working on this... But it sounds to me like PostgreSQL already *has* a "complex type" to represent the tuples in your table, and you just need to find out what the name of that "complex type" might be. It may even turn out to be just the table name or something...

A "complex", or aka "composite", type in PostgreSQL is represented by the name of a table or view. The capability to return "setof a-composite-type" exists in a limited way in PostgreSQL 7.2.x. See the thread at: http://archives.postgresql.org/pgsql-interfaces/2002-06/msg00042.php for a recent discussion about this with some examples. PostgreSQL 7.3, when it is released, will have much better capability. You will be able to do: test=# select * from getfoo(1); fooid | f2 -------+-----

      1 |  11
      1 | 111

(2 rows) In cvs HEAD you can do this already with SQL language functions and C language functions. HTH, Joe