Lists: | pgsql-general |
---|
From: | "Fernando Papa" <fpapa(at)claxson(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Table functions say "no destination for result data." |
Date: | 2002-12-06 20:20:03 |
Message-ID: | CB94A4924490EC4A81EDA55BA378B7BA7B848F@exch2k01.buehuergo.corp.claxson.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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 '
DECLARE
vid ALIAS FOR $1;
result int8;
vnick varchar;
vsex varchar;
vdesde int8;
vhasta int8;
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
;
return;
END;
' LANGUAGE 'plpgsql' VOLATILE;
So, I pass to the function a number. Inside the function, first I get
some information (select into) about the search profile, and then I do
the select who want to return (vw_match it's a view who have the same
structure of the second select).
The problem is, when I try to execute this function (in PHP) I get this
message:
SELECT match_tf(132);
Warning: pg_query() query failed: ERROR: SELECT query has no destination
for result data. If you want to discard the results, use PERFORM
instead. in /usr/local/apache/htdocs/postgres/ap_tf.php on line 17
I see this in postgresql logfile:
2002-12-06 17:00:13 ERROR: SELECT query has no destination for result
data.
If you want to discard the results, use PERFORM instead.
2002-12-06 17:00:13 WARNING: Error occurred while executing PL/pgSQL
function match_tf
The same error gave me if I try on psql:
cont=# select match_tf(132);
WARNING: Error occurred while executing PL/pgSQL function match_tf
WARNING: line 21 at SQL statement
ERROR: SELECT query has no destination for result data.
If you want to discard the results, use PERFORM instead.
cont=#
What is the problem? Is this a good use of table function?
Thanks in advance!
--
Fernando O. Papa
From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
---|---|
To: | Fernando Papa <fpapa(at)claxson(dot)com> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Table functions say "no destination for result data." |
Date: | 2002-12-06 21:20:07 |
Message-ID: | 20021206131411.C20609-100000@megazone23.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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.
From: | Joe Conway <mail(at)joeconway(dot)com> |
---|---|
To: | Fernando Papa <fpapa(at)claxson(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Table functions say "no destination for result data." |
Date: | 2002-12-06 21:22:03 |
Message-ID: | 3DF114FB.8070802@joeconway.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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