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* <[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>> > 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 ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org