Yes, of course.... But this function does not do what I need it to do... I want x rows returned, but instead I just get a stringname... Either how do I use this name or how do I return x rows? BTJ Gnanavel S wrote: > 'IF' block is not ended. > > On 9/15/05, *Bjørn T Johansen* <btj@xxxxxxxxxx <mailto:btj@xxxxxxxxxx>> > wrote: > > I am trying to write a function that returns x rows, where x >= 0 > and this is what I > have come up with...: > > CREATE OR REPLACE FUNCTION trykkStatus (pressID SMALLINT) RETURNS > REFCURSOR AS ' > declare > orderID ordrenew.id%TYPE; > cur REFCURSOR; > begin > select id into orderID from ordrenew where now() between trykkstart > and produsert and > presseid = $1 limit 1; > if not found then > raise exception ''No rows''; > open cur for ((select 1 as colid, id, trykkstart, produsert, > presseid from ordrenew > where produsert < (select trykkstart from ordrenew where id=orderID) > order by produsert desc limit 1) > union > (select 2 as colid, id, trykkstart, produsert, presseid from > ordrenew where now() > between trykkstart and produsert and presseid = 1 limit 1) > union > (select 3 as colid, id, trykkstart, produsert, presseid from > ordrenew where > trykkstart > (select produsert from ordrenew where id=orderID) order > by trykkstart > limit 1) order by colid); > return(cur); > END; > ' LANGUAGE 'plpgsql'; > > > But this just gives me the following error: > > syntax error at or near ";" at character 851 > > And I can't find anything wrong near any ; ....? > Also, does this function do what I expect it to do? > And instead of rasing an error when no rows is found, can I return > an "empty" cursor > instead? > > > Regards, > > BTJ > > -- > ----------------------------------------------------------------------------------------------- > > Bjørn T Johansen > > btj@xxxxxxxxxx <mailto:btj@xxxxxxxxxx> > ----------------------------------------------------------------------------------------------- > Someone wrote: > "I understand that if you play a Windows CD backwards you hear > strange Satanic messages" > To which someone replied: > "It's even worse than that; play it forwards and it installs Windows" > ----------------------------------------------------------------------------------------------- > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: explain analyze is your friend > > > > > -- > with regards, > S.Gnanavel > Satyam Computer Services Ltd. -- ----------------------------------------------------------------------------------------------- Bjørn T Johansen (BSc,MNIF) Executive Manager btj@xxxxxxxxxx Havleik Consulting Phone : +47 21 69 15 20 Bjørnebærstien 57 Fax : +47 41 13 09 15 N-1348 Rykkinn Cellular : +47 926 93 298 http://www.havleik.no ----------------------------------------------------------------------------------------------- Someone wrote: "I understand that if you play a Windows CD backwards you hear strange Satanic messages" To which someone replied: "It's even worse than that; play it forwards and it installs Windows" ----------------------------------------------------------------------------------------------- ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org
