Returning resultset through a function in postgresql (version 7.1.3)

[Tis]

Hi,
Problem: A PostGreSQL function returns an ID when specifying return type as 'RETURNS SET OF <tablename>'. How does one use this ID to get access to the actual object containing data. I have tried the following using PostGresql and Java but am getting the error as given below.
Thanks in advance.

CREATE FUNCTION foo()
RETURNS SETOF dept
AS '
SELECT * FROM dept;
' LANGUAGE 'sql';

This function is returning an Objectid for each row in the emp table.
(the function is returning same Objectid for each row)
I tried reading the object using LargeObject class in package postgresql.largeobject passing the above objectid.
it's giving me following error

FastPath call returned ERROR: inv_open: large object 136440936 not found

at org.postgresql.fastpath.Fastpath.fastpath(Fastpath.java:141)
at org.postgresql.fastpath.Fastpath.fastpath(Fastpath.java:191)
at org.postgresql.fastpath.Fastpath.getInteger(Fastpath.java:203)
at org.postgresql.largeobject.LargeObject.<init>(LargeObject.java:89)
at org.postgresql.largeobject.LargeObjectManager.open(LargeObjectManager.java:147)

 

[Guest_imported]

I run into exactly the same situation - tried &quot;AS&quot;, getting all columns instead of &quot;*&quot;, tried the built-in type &quot;SET&quot; with no avail. Has anyone ever succeeded in getting actual table contents thru stored procedures with language 'sql'?

 

[Guest_imported]

Hi,

at the moment I have same problem. So last post was
in Dec. Is problem solved? I need answer, too.

Thanks for help.

Thomas
thomas.hoerner@proudsky.de

 

[rycamor]

Maybe I'm wrong on this, but I though SETOF just made it possible to return an array instead of one single value. Returning a whole recordset is different, though, because each row is an array, and then you iterate through all the rows, returning each array (row). Supposedly some people have been able to return result sets with a little more tweaking and work-arounds, but the functionality is pretty quirky at the moment.

Tom Lane mentions in the PostgreSQL mailing list that the code for returning result sets is &quot;... fairly disheveled at the moment, having suffered a lot of bit-rot and no attention since Berkeley days ... &quot;

Translation: don't depend on result sets from functions at the moment, just use views if you need to return a full result set. Fortunately they are planning to re-implement this concept in another way soon, where you could just do something like:

&quot;select field1, field2 from test_func('foo') as results;&quot;

(Above quotes and example taken from pgsql-sql@postgresql.org mailing list. You can search that list at

http://archives.postgresql.org/)

-------------------------------------------

&quot;Calculus is just the meaningless manipulation of higher symbols&quot;
                          -unknown F student