Oki, I found a way... begin; select trykkstatus(1,'refcurs'); (I have added one parameter to know the cursor name) fetch all from refcurs; commit;
But this returns two rowsets, first one for the select and then one for the fetch, but how do I get rid of the row that is returned by the select? BTJ Bjørn T Johansen wrote: > 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* <[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>> >>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 >> >> [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> >> >> ----------------------------------------------------------------------------------------------- >> 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. > > ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
