Table functions say "no destination for result data."

On Fri, 6 Dec 2002, Fernando Papa wrote:

Hi everybody!

I'mt playing with new table functions on a fresh postgresql 7.3 over
Solaris... I want a function who return several rows, so I define that:

-- Function: public.matcheo_cupido_tf(int8)
CREATE FUNCTION public.match_tf(int8) RETURNS public.vw_match AS '

If you want to return multiple rows you want
RETURNS SETOF public.vw_match

DECLARE
vid ALIAS FOR $1;
result int8;
vnick varchar;
vsex varchar;
vdesde int8;
vhasta int8;

(add another local, see below)
r record;

BEGIN

select into vnick,vsex,vdesde,vhasta
par.nick,par.sexo,pb.edaddesde,pb.edadhasta,pb.pais
from participantes par,
perfilesbusqueda pb
where par.identificador = vid and
pb.participante = par.identificador;

select pp.participante,par.nick,pp.sex,pp.edad,pp.desc
from perfilespropios pp,
participantes par
where pp.sex <> vsex and
pp.edad >= vdesde and
pp.edad <= vhasta and
par.identificador = pp.participante
;

You want something like:

for r in select ... loop
return next r;
end loop;

return;

I believe.

Fernando Papa wrote:

I'mt playing with new table functions on a fresh postgresql 7.3 over
Solaris... I want a function who return several rows, so I define that:

You need to re-read the manual on this. See (at least):
http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html
Basically you need to select into a record type variable in a loop, and use
RETURN NEXT.

Here's an unrelated working example you can study:
CREATE TABLE payments (r_date_payment TIMESTAMP, r_description VARCHAR(50),
r_value numeric (12,2));

INSERT INTO payments VALUES(CURRENT_TIMESTAMP, 'a', '12.50');
INSERT INTO payments VALUES(CURRENT_TIMESTAMP, 'b', '11.75');
INSERT INTO payments VALUES(CURRENT_TIMESTAMP, 'c', '-99.99');

CREATE OR REPLACE FUNCTION my_proc(TIMESTAMP)
RETURNS SETOF payments
AS '
DECLARE
rec RECORD;
BEGIN
FOR rec IN SELECT * FROM payments
WHERE r_date_payment BETWEEN $1 AND CURRENT_TIMESTAMP LOOP
IF rec.r_value < 0 THEN
rec.r_value = rec.r_value*-1;
END IF;
RETURN NEXT rec; /* Each RETURN NEXT command returns a row */
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql';

test=# select * from payments;
r_date_payment | r_description | r_value
----------------------------+---------------+---------
2002-10-22 10:27:38.086554 | a | 12.50
2002-10-22 10:27:38.172964 | b | 11.75
2002-10-22 10:27:38.177543 | c | -99.99
(3 rows)

test=# SELECT * FROM my_proc('01/01/2002');
r_date_payment | r_description | r_value
----------------------------+---------------+---------
2002-10-22 10:27:38.086554 | a | 12.50
2002-10-22 10:27:38.172964 | b | 11.75
2002-10-22 10:27:38.177543 | c | 99.99
(3 rows)

Here's a slightly different approach:

CREATE OR REPLACE FUNCTION show_group(text) RETURNS SETOF text AS '
DECLARE
loginname text;
low int;
high int;
BEGIN
SELECT INTO low
replace(split_part(array_dims(grolist),'':'',1),''['','''')::int
FROM pg_group WHERE groname = $1;
SELECT INTO high
replace(split_part(array_dims(grolist),'':'',2),'']'','''')::int
FROM pg_group WHERE groname = $1;

FOR i IN low..high LOOP
SELECT INTO loginname s.usename
FROM pg_shadow s join pg_group g on s.usesysid = g.grolist[i];
RETURN NEXT loginname;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql';

regression=# select * from show_group('grp1');
show_group
------------
postgres
testuser
robot
(3 rows)

HTH,

Joe

Hi Stephan!
I read your comments:

If you want to return multiple rows you want
RETURNS SETOF public.vw_match

I don't know why pgAdmin doesn't show RETURNS SETOF... I put it, but
when I try to see DDL on pgAdmn I see without "SETOF"... maybe a bug?

for r in select ... loop
return next r;
end loop;

I put this thing (it's really a new concept... I'm coming from oracle
and I never seen things like that) on function.

I make a very simple version of these function just for familiarize
about the way to do this:

CREATE FUNCTION public.match_tf(int8) RETURNS SETOF public.vw_matcheo AS
'
DECLARE
vid ALIAS FOR $1;
vcursor refcursor;
r record;
BEGIN

for r in select
pp.participante,par.nick,pp.sexo,pp.edad,pp.pais,pp.descripcionbreve
from perfilespropios pp,
participantes par
where par.identificador = pp.participante
limit 5
loop
return next r;
end loop;

return;

END;
' LANGUAGE 'plpgsql'

And I get another error this time:

2002-12-09 12:04:40 ERROR: Set-valued function called in context that
cannot accept a set
2002-12-09 12:04:40 WARNING: Error occurred while executing PL/pgSQL
function match_tf
2002-12-09 12:04:40 WARNING: line 31 at return next

I called the function:
contenedor=# select match_tf(132);
WARNING: Error occurred while executing PL/pgSQL function match_tf
WARNING: line 13 at return next
ERROR: Set-valued function called in context that cannot accept a set

could be the problem the view?

Thanks a lot!

--
Fernando O. Papa

Sorry Stefan and all listers...

I made this:
contenedor=# select match_tf(132);

but I must do this:
contenedor=# select * from match_tf(132);

--
Fernando O. Papa
DBA