Re: Help trying to write my first plpgsql function... - Mailing list pgsql-general

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@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 6: explain analyze is your friend