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
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
