Re: Help trying to write my first plpgsql function... - Mailing list pgsql-general

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