Help trying to write my first plpgsql function...

From: Bjørn T Johansen <btj(at)havleik(dot)no>
To: PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject: Help trying to write my first plpgsql function...
Date: 2005-09-15 08:51:21
Message-ID: 43293609.8040509@havleik.no
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(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"
-----------------------------------------------------------------------------------------------

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Gnanavel S 2005-09-15 09:06:01 Re: Help trying to write my first plpgsql function...
Previous Message Janning Vygen 2005-09-15 08:24:42 Re: ERROR: type "temp_gc" already exists