Re: options for no multiple rows? - Mailing list pgsql-sql

One approach that might be satisfactory for you, using
version 7.1:
1. Have your SP assemble the desired rows, and insert
them in a temporary table (which the procedure could
create dynamically, using EXECUTE)
2. Select * from temporary_table;

This will add one extra line of code to your app, and
will get an equivalent result to returning the
resultset directly.

It is said to be possible to return a resultset from a
C function, but that is beyond my competence to
comment on.

--- pgsql <pgsqllist@mail.rineco.com> wrote:
> Greets!
> 
> Ok, from what I've read (good, bad or indifferent)
> you can't create a
> stored procedure/function and return multiple rows
> to use in say a
> resultset with the following:
> 
> Select     a.T1_FILED_1
>     ,a.T1_FIELD_2
>     ,a.T1_FIELD_3
>     ,b.T2_FIELD_1
>     ,b.T2_FIELD_2
> From T1 a
> Inner Join T2 b On a.T1_FIELD_1 = b.T2_FIELD_1
> Where a.T1_FIELD_2 = @ParmPassedIn
> 
> So my question is, what are my/our alternatives if
> any?  What could I do
> as opposed to putting the above "dynamic" SQL in my
> app?
> 
> I've never used "dynamic" SQL in my apps b/c it
> always seems to be a pain
> to test and maintain as opposed to creating a stored
> procedure on
> something like MS SQL and simply modifying the SP
> w/out having to
> re-compile an app....hopefully anyhow?  ;-)
> 
> I've seen quite a few posts about the issue and I'm
> sure it's a sore topic
> when someone brings it up, but I guess my question
> is what alternatives
> does one have?  I've seen lots of replies that state
> "No, you can't do
> that", but I haven't come across any solutions, or
> perhaps I just don't
> know what I should be looking for.
> 
> I'd very much like to use PostgreSQL in something a
> little more serious
> than I am now, but I'm not really sure how I'm going
> to handle the
> lack of multiple row issue(s).
> 
> Anyone willing to share
> examples/suggestions/comments on how they get
> around the issue?
> 
> I can't imagine that if there *would be* 'XX' rows
> returned that I would
> need to create the same stored procedure/function
> that would only return 1
> at a time and call it 'XX' times, but perhaps this
> is the best I can do?
> 
> I thought a while prior to posting this b/c there
> are things available via
> PostgreSQL that other packages couldn't even touch
> and I can't wait to
> really start tinkering around.  I know a lot of
> blood, sweat and tears
> (mostly I'm sure) have gone into this and it really
> makes one feel bad to
> bring up anything negative in regards to what *IS*
> offered.
> 
> Apologies and Best Regards,
> -tim
> 
> 
> 
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster


__________________________________________________
Do You Yahoo!?
Great stuff seeking new owners in Yahoo! Auctions! 
http://auctions.yahoo.com