Thread: options for no multiple rows?
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
On Sat, 26 Jan 2002, 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: Depending on what you're doing (and if you're willing to work with the 7.2rcs or wait for it), 7.2 allows you to define functions that return cursors that you can then fetch from within the transaction you called the function in, so you should be able to do a sequence like: begin; select * from func(param); -- get back name of cursor, say "<unnamed cursor 1>" -- fetch 10 from "<unnamed cursor 1>"; fetch 10 from "<unnamed cursor 1>"; close "<unnamed cursor 1>"; commit; I don't think this is quite a complete replacement. AFAIK, you can't use the cursor like a table (ie in later joins and such), but that may not be necessary for what you're doing. (In case you're wondering, my test function looked like: create function ct(int) returns refcursor as 'declare curs1 refcursor; begin open curs1 for select * from cttable where key= $1; return curs1; end;' language 'plpgsql'; )
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
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 >
Greets, I guess I should have been a little more specific as opposed to the example, but it's not *just* multiple rows from a function, you can. I know if returning truly one column this is fine, but calling a function that returns multi/multi (fields/rows) in say an RDO resultset currently cannot be done, or at least not as it would be if I was calling a Stored Procedure on a M$ SQL Server. Best Regards, -tim --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.314 / Virus Database: 175 - Release Date: 1/11/02
"pgsql" <pgsqllist@mail.rineco.com> wrote in message news:Pine.LNX.4.21.0201262203530.25983-100000@mail.rineco.com... > 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: > I've been searching for a solution to this problem too. Until now I've used MSSQL where multiple rows (or even multiple resultsets) are't any problem inside a sp. I've thought about it and came up with the following idea. NOTE: I haven't tested this!!!!! Create a temporay table with the resultset you want. Fill the table inside the sp Do a select on the temporay table. I don't now if it will work, but it looks okay. regards Yvo