On 9/15/05, Bjørn T Johansen <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
-----------------------------------------------------------------------------------------------
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.
