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"
-----------------------------------------------------------------------------------------------
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 |