Function returning setof

[Vladimir M]

Hi

I am writing a function with PL/pgSQL, which returns result of some complex query with several tables. In manual i found such example:

CREATE OR REPLACE FUNCTION Test() RETURNS SETOF table1 AS $$
DECLARE	ret_row RECORD;
BEGIN
	FOR ret_row IN SELECT * FROM table1 LOOP
		RETURN NEXT ret_row;
	END LOOP;
	RETURN;
END;
$$ LANGUAGE 'plpgsql';

But this code uses simple query SELECT * FROM table1 with single table.

I don't undestand how to write RETURNS SETOF .... in case with a more complex query as this:

SELECT a.name AS address_name, g.name AS goods_name, g.unit AS goods_unit, g.price AS goods_price
FROM set s 
          INNER JOIN goods g ON s.id_goods = g.id
          INNER JOIN address a ON s.id_address = a.id

Thank you.

[Vladimir M]

I must to add that i need a function, because my query may be with some parameters or with some processing.

[michaelb]

Let's consider this case:

create table packages (
    id serial primary key,
    pkg_name varchar(64),
    UNIQUE (pkg_name)
);
    
create table components (
    pkg_id integer REFERENCES packages(id),
    comp_name varchar(64), 
    UNIQUE (pkg_id, comp_name)
);

Tables have this data:

postgres=# select * from packages;
 id | pkg_name
----+----------
  1 | Bronze
  2 | Silver
  3 | Gold
  4 | Platinum
(4 rows)

postgres=# select * from components;
 pkg_id | comp_name
--------+-----------
      1 | Humble
      2 | Humble
      2 | Moderate
      3 | Humble
      3 | Moderate
      3 | Advanced
      4 | Humble
      4 | Moderate
      4 | Advanced
      4 | Superior
(10 rows)

When I tried to write a function I thought that it would be sufficient to specify return type as "SETOF RECORD", hoping that field definition will be implicitly derived from the select statement, but I ended up with error.
(I tested it on Windows with Postgresql 8.2.3)
I did not have a chance to research this, instead this is the canonical approach - create a type which corresponds to the record returned by my function:
(Alternatively I could create a view and make SETOF refer to it)

create type t_fret as ( pkg varchar(64), comp varchar(64) );

Now I can create a function:

CREATE OR REPLACE FUNCTION tf1(integer) RETURNS SETOF t_fret AS $$
   DECLARE rec t_fret%ROWTYPE;
   begin
       FOR rec IN 
           SELECT p.pkg_name, c.comp_name FROM packages p 
                  INNER JOIN components c on p.id = c.pkg_id
           WHERE P.ID = $1 LOOP
           -- additional processing if any goes here --
       return next rec;
       END LOOP;
       return;
   end;
$$ LANGUAGE plpgsql;

This seems to work fine:

postgres=# select * from tf1(1);
  pkg   |  comp
--------+--------
 Bronze | Humble
(1 row)

postgres=# select * from tf1(4);
   pkg    |   comp
----------+----------
 Platinum | Humble
 Platinum | Moderate
 Platinum | Advanced
 Platinum | Superior
(4 rows)

Hopefully you can use this example to make what you need.