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

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(at)havleik(dot)no <mailto: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 <mailto: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.

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