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

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

'IF' block is not ended.




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

On 9/15/05, Bjørn T Johansen <btj@havleik.no> wrote:
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 <mailto: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

On 9/15/05, Bjørn T Johansen <btj@havleik.no> wrote:
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/15/05, *Bjørn T Johansen* <btj@havleik.no <mailto: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

On 9/19/05, Bjørn T Johansen <btj@havleik.no> wrote:
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