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

From Allan Engelhardt
Subject Re: options for no multiple rows?
Date
Msg-id 3C55C2D9.3050506@cybaea.com
Whole thread Raw
In response to options for no multiple rows?  (pgsql <pgsqllist@mail.rineco.com>)
List pgsql-sql
When I worked with Sybase the workaround was to use a temporary table to store 
the resultset.

Hope this helps a little.

Allan./

pgsql 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
> 



pgsql-sql by date:

Previous
From: Jean-Luc Lachance
Date:
Subject: xbase2pg 1.0-1 vs postgres 7.2b5 compatibility
Next
From: Allan Engelhardt
Date:
Subject: Re: export/save meta data