Thread: What's the equivalent in PL/pgSQL
Hi, I'm trying to find an equivalent plpgsql function as this: func x returns SETOF "Tablename" AS ' Select * from "Tablename"; ' language sql How is this accomplished with plpgsql while not using a loop or a second and third temporal table?
See this section of the manual: http://www.postgresql.org/docs/8.0/interactive/plpgsql-control- structures.html In particular, look at 35.7.1.2 and 35.7.4. I think you need to loop through the results of the query in order to return them. If you just want to return the result set as a whole to another function that needs to work with the data, you can use a cursor. Sean On Jan 27, 2005, at 7:46 AM, KÖPFERL Robert wrote: > Hi, > I'm trying to find an equivalent plpgsql function as this: > > func x returns SETOF "Tablename" AS > ' > Select * from "Tablename"; > ' language sql > > > How is this accomplished with plpgsql while not using a loop or a > second and > third temporal table? > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
That's bad. Is there really no ohter way? So it takes TWO termoral tables. Or even more? And it can't be just sql because theres more around that statement. > -----Original Message----- > From: Sean Davis [mailto:sdavis2@mail.nih.gov] > Sent: Donnerstag, 27. Jänner 2005 14:57 > To: KÖPFERL Robert > Cc: pgsql-sql@postgresql.org > Subject: Re: [SQL] What's the equivalent in PL/pgSQL > > > See this section of the manual: > > http://www.postgresql.org/docs/8.0/interactive/plpgsql-control- > structures.html > > In particular, look at 35.7.1.2 and 35.7.4. I think you need > to loop > through the results of the query in order to return them. If > you just > want to return the result set as a whole to another function > that needs > to work with the data, you can use a cursor. > > Sean > > On Jan 27, 2005, at 7:46 AM, KÖPFERL Robert wrote: > > > Hi, > > I'm trying to find an equivalent plpgsql function as this: > > > > func x returns SETOF "Tablename" AS > > ' > > Select * from "Tablename"; > > ' language sql > > > > > > How is this accomplished with plpgsql while not using a loop or a > > second and > > third temporal table? > > > > ---------------------------(end of > > broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org >
On Jan 27, 2005, at 11:10 AM, KÖPFERL Robert wrote: > That's bad. > Is there really no ohter way? > So it takes TWO termoral tables. Or even more? I'm not sure what is bad. In what sense is it bad? How does more than one table come into things? > And it can't be just sql because theres more around that statement. I'm not sure what you mean. Which statement? >> -----Original Message----- >> From: Sean Davis [mailto:sdavis2@mail.nih.gov] >> Sent: Donnerstag, 27. Jänner 2005 14:57 >> To: KÖPFERL Robert >> Cc: pgsql-sql@postgresql.org >> Subject: Re: [SQL] What's the equivalent in PL/pgSQL >> >> >> See this section of the manual: >> >> http://www.postgresql.org/docs/8.0/interactive/plpgsql-control- >> structures.html >> >> In particular, look at 35.7.1.2 and 35.7.4. I think you need >> to loop >> through the results of the query in order to return them. If >> you just >> want to return the result set as a whole to another function >> that needs >> to work with the data, you can use a cursor. >> >> Sean >> >> On Jan 27, 2005, at 7:46 AM, KÖPFERL Robert wrote: >> >>> Hi, >>> I'm trying to find an equivalent plpgsql function as this: >>> >>> func x returns SETOF "Tablename" AS >>> ' >>> Select * from "Tablename"; >>> ' language sql >>> >>> >>> How is this accomplished with plpgsql while not using a loop or a >>> second and >>> third temporal table? >>> >>> ---------------------------(end of >>> broadcast)--------------------------- >>> TIP 6: Have you searched our list archives? >>> >>> http://archives.postgresql.org >> > > ---------------------------(end of > broadcast)--------------------------- > TIP 8: explain analyze is your friend