Help trying to write my first plpgsql function...

'IF' block is not ended.

On 9/15/05, Bjørn T Johansen <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

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

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

Oki, I found a way...

begin;
select trykkstatus(1,'refcurs'); (I have added one parameter to know the cursor name)
fetch all from refcurs;
commit;

But this returns two rowsets, first one for the select and then one for the fetch,
but how do I get rid of the row that is returned by the select?

BTJ

Bj�rn T Johansen wrote:

On 9/15/05, Bjørn T Johansen <btj@havleik.no> wrote:

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?

for refcursors - just use returned name in subsequent "FETCH FROM
<this_name>";

for set returning functions you have to declare them as "returns set of
SOMETHING"
and then use return next SOMETHING;
insetead of return.

read the docs - it's quite good reading.

depesz

Yes, I did and I found an answer... :)

But I am trying to use this function in a report designer and the result from the
select is in the way of the real data from the fetch... Is there a way around this?

BTJ

hubert depesz lubaczewski wrote:

On 9/15/05, Bjørn T Johansen <btj@havleik.no> wrote:

Yes, I did and I found an answer... :)

you did what? read the docs?

But I am trying to use this function in a report designer and the result

from the
select is in the way of the real data from the fetch... Is there a way
around this?

and?
as i said: define the function as setof something.
for example (i'm wrinting without testnig, so there might be small mistakes)

create type srf_type_1 ( field1 int4, field2 text);

create or replace function test () returns setof srf_type_1 as
$BODY$
declare
temprec srf_type_1%ROWTYPE;
begin
for temprec in select id as field1, username as field2 from users where
is_active = true loop
return next temprec;
end loop;
return;
end;
$BODY$
language 'plpgsql';

should work as select * from test();

hubert

Yes, I read the doc...

And I have now created this function, which seems to be ok but when I try to select
from it, I get an error telling me that "subquery must return only one column". But
my subquery does return only one column...?

My function looks like this?

CREATE OR REPLACE FUNCTION trykkStatus (pressID INTEGER)
RETURNS SetOf trykkstatus_type AS '
DECLARE
orderID ordrenew.id%TYPE;
tmprec trykkstatus_type%ROWTYPE;
BEGIN
select id into orderID from ordrenew where now() between trykkstart and produsert and
presseid = pressID limit 1;
if not found then
return;
end if;
select into tmprec (select 1 as colid, ordrenew.id, trykkstart, produsert,
presseid,product.name from ordrenew left outer join product on ordrenew.productid =
product.id where produsert < (select trykkstart from ordrenew where id=orderID) and
presseid = pressID order by produsert desc limit 1);
return next tmprec;
select into tmprec (select 2 as colid, ordrenew.id, trykkstart, produsert,
presseid,product.name from
ordrenew left outer join product on ordrenew.productid = product.id where ordrenew.id
= orderID);
return next tmprec;
select into tmprec (select 3 as colid, ordrenew.id, trykkstart, produsert,
presseid,product.name from
ordrenew left outer join product on ordrenew.productid = product.id where trykkstart >
(select produsert from ordrenew where id=orderID) and presseid = pressID order by
trykkstart limit 1) order by colid);
return next tmprec;
END;
' LANGUAGE 'plpgsql';

What am I missing?

BTJ

hubert depesz lubaczewski wrote:

On 9/19/05, Bjørn T Johansen <btj@havleik.no> wrote:

CREATE OR REPLACE FUNCTION trykkStatus (pressID INTEGER)
RETURNS SetOf trykkstatus_type AS '
DECLARE
orderID ordrenew.id%TYPE;
tmprec trykkstatus_type%ROWTYPE;
BEGIN
select id into orderID from ordrenew where now() between trykkstart and
produsert and
presseid = pressID limit 1;
if not found then
return;
end if;
select into tmprec (select 1 as colid, ordrenew.id <http://ordrenew.id&gt;,
trykkstart, produsert,
presseid,product.name <http://product.name&gt; from ordrenew left outer join
product on ordrenew.productid =
product.id <http://product.id&gt; where produsert < (select trykkstart from
ordrenew where id=orderID) and
presseid = pressID order by produsert desc limit 1);

why do you use subselect here?
just do:
select 1 as colid, ordenew.id..... into tmprec from ordrenew left ....;

return next tmprec;

END;

dont forget to add "return;" before END;

depesz

I am not sure why I used subselect, I just saw an example and followed it..

But now it's working as it should... Thx for all the help! :)

BTJ

On 9/19/05, Bj�rn T Johansen <btj ( at ) havleik ( dot ) no> wrote:

CREATE OR REPLACE FUNCTION trykkStatus (pressID INTEGER)
RETURNS SetOf trykkstatus_type AS '
DECLARE
orderID ordrenew.id%TYPE;
tmprec trykkstatus_type%ROWTYPE;
BEGIN
select id into orderID from ordrenew where now() between trykkstart and produsert and
presseid = pressID limit 1;
if not found then
return;
end if;
select into tmprec (select 1 as colid, ordrenew.id, trykkstart, produsert,
presseid, product.name from ordrenew left outer join product on ordrenew.productid =
product.id where produsert < (select trykkstart from ordrenew where id=orderID) and
presseid = pressID order by produsert desc limit 1);

why do you use subselect here?
just do:
select 1 as colid, ordenew.id..... into tmprec from ordrenew left ....;

return next tmprec;
END;

dont forget to add "return;" before END;

depesz