Re: returning a recordset from PLpg/SQL - Mailing list pgsql-sql

Terence Kearns wrote:

> Tom Lane wrote:
>
> > Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> >
> >> On Tue, 2 Mar 2004, Terence Kearns wrote:
> >>
> >>> Well I haven't yet done anything because I couldn't get anything to
> >>> compile which returned SETOF RECORD..
> >
> >
> >
> >> As a starting point, SETOF "RECORD" is different from SETOF RECORD 
> given
> >> PostgreSQL's fold case to lower case for unquoted names.
> >
> >
> >
> > Also, you can hardly expect a function to return a rowtype that doesn't
> > even exist until the function executes --- how the heck is the parser
> > supposed to make sense of the calling query?  So the "execute create
> > type" part of this is nonsense, I'm afraid.
>
>
> Right you are. I did mention that I didn't expect that code to work at 
> all, I just used it as an indicator or a desired outcome. I'm sorry if 
> I didn't make that clear enough. I just hoped that it would illustrate 
> what I'm trying to achieve. And that is:
> "return a set of rows where the columns in that row are not yet 
> determined."
>
> > The SETOF RECORD mechanism
>
> > will let you return a rowtype that is not known fully at the time the
> > function is written, but the rowtype does have to be known when the
> > calling query is parsed.
>
>
> Interesting.
>
> >
> > You might be able to replace the CREATE TYPE with an anonymous record
> > type in the calling query:
> >
> >     select ...
> >     from details_for_profile(...) as x(doc_id int4,
> >                                        doc_title varchar(256),
> >                                        ...);
> >
> >             regards, tom lane
> >
>
> yeah but then you're back to square one with dynamically building the 
> columns to match the anonymous type you declared in the query.
>
> I've got some ideas about creating persistent types using RULEs on the 
> attribute_profiles table. So when someone INSERTs or UPDATEs an 
> attribute profile, a datatype going by the name 'profile_type_' || 
> att_profile_id::text is created. That way the types are already known 
> and maybe can somehow be passed to the details_for_profile() prcedure.
>
> I'll have to experiment and get back.
>

I'm sorry if this is just plain stupid - as I've not bothered to read 
the full schema in your message - but wouldn't a cursor do ?
Since I'm mostly coding in Java, I find that this approach, while common 
to both PGSQL and Oracle, works quite good. I've not
been able to use the cursor in psql although... By the way can someone 
tell me how the heck am I supposed to get the results
from a cursor return from a pl/pgsql function ?

Example:
create or replace function SomeObject_read(int) returns refcursor as       '       declare               the_row
refcursor;              v_id alias for $1;       begin               open the_row for                       select *
                  from SomeObject                       where id = v_id;               return the_row;       end;
'language plpgsql;
 
This works great from Java where I use a callable statement, do a "{ ? = 
call SomeObject_read(?) }" query,
register the out parameter as OTHER, bind the parameter and get back a 
ResultSet.
All fine and marry, but I can't figure out how to use _this_ form of the 
function from psql. I've read and tried
the manual examples and they do work. However, I can't make this one work.

Thanks in advance,

-- 
Radu-Adrian Popescu
CSA, DBA, Developer
Aldratech Ltd.
+40213212243