Re: Help trying to write my first plpgsql function...

'IF' block is not ended.

On 9/15/05, Bjørn T Johansen <btj(at)havleik(dot)no> 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(at)havleik(dot)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
>

--
with regards,
S.Gnanavel
Satyam Computer Services Ltd.