Skip site navigation (1) Skip section navigation (2)

Re: Table functions say "no destination for result data."

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 (view raw or flat)
Thread:
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


In response to

pgsql-general by date

Next:From: Jeff BoesDate: 2002-12-06 21:26:47
Subject: Re: Compatibility of future releases
Previous:From: Stephan SzaboDate: 2002-12-06 21:20:07
Subject: Re: Table functions say "no destination for result data."

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group