Search Postgresql Archives

Help trying to write my first plpgsql function...

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxx
-----------------------------------------------------------------------------------------------
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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux