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

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@havleik.no <mailto:btj@havleik.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@havleik.no <mailto: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
>
>
>
>
> --
> with regards,
> S.Gnanavel
> Satyam Computer Services Ltd.

--
-----------------------------------------------------------------------------------------------
Bjørn T Johansen (BSc,MNIF)
Executive Manager
btj@havleik.no            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"
-----------------------------------------------------------------------------------------------