By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,374 Members | 1,768 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,374 IT Pros & Developers. It's quick & easy.

Function returning setof

P: 4
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:

Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE FUNCTION Test() RETURNS SETOF table1 AS $$
  2. DECLARE    ret_row RECORD;
  3. BEGIN
  4.     FOR ret_row IN SELECT * FROM table1 LOOP
  5.         RETURN NEXT ret_row;
  6.     END LOOP;
  7.     RETURN;
  8. END;
  9. $$ LANGUAGE 'plpgsql';
  10.  
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT a.name AS address_name, g.name AS goods_name, g.unit AS goods_unit, g.price AS goods_price
  2. FROM set s 
  3.           INNER JOIN goods g ON s.id_goods = g.id
  4.           INNER JOIN address a ON s.id_address = a.id
  5.  
Thank you.
May 6 '07 #1
Share this Question
Share on Google+
2 Replies


P: 4
I must to add that i need a function, because my query may be with some parameters or with some processing.
May 6 '07 #2

Expert 100+
P: 534
Let's consider this case:

Expand|Select|Wrap|Line Numbers
  1. create table packages (
  2.     id serial primary key,
  3.     pkg_name varchar(64),
  4.     UNIQUE (pkg_name)
  5. );
  6.  
  7. create table components (
  8.     pkg_id integer REFERENCES packages(id),
  9.     comp_name varchar(64), 
  10.     UNIQUE (pkg_id, comp_name)
  11. );
  12.  
Tables have this data:
Expand|Select|Wrap|Line Numbers
  1. postgres=# select * from packages;
  2.  id | pkg_name
  3. ----+----------
  4.   1 | Bronze
  5.   2 | Silver
  6.   3 | Gold
  7.   4 | Platinum
  8. (4 rows)
  9.  
  10. postgres=# select * from components;
  11.  pkg_id | comp_name
  12. --------+-----------
  13.       1 | Humble
  14.       2 | Humble
  15.       2 | Moderate
  16.       3 | Humble
  17.       3 | Moderate
  18.       3 | Advanced
  19.       4 | Humble
  20.       4 | Moderate
  21.       4 | Advanced
  22.       4 | Superior
  23. (10 rows)
  24.  
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)

Expand|Select|Wrap|Line Numbers
  1. create type t_fret as ( pkg varchar(64), comp varchar(64) );
  2.  
Now I can create a function:
Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE FUNCTION tf1(integer) RETURNS SETOF t_fret AS $$
  2.    DECLARE rec t_fret%ROWTYPE;
  3.    begin
  4.        FOR rec IN 
  5.            SELECT p.pkg_name, c.comp_name FROM packages p 
  6.                   INNER JOIN components c on p.id = c.pkg_id
  7.            WHERE P.ID = $1 LOOP
  8.            -- additional processing if any goes here --
  9.        return next rec;
  10.        END LOOP;
  11.        return;
  12.    end;
  13. $$ LANGUAGE plpgsql;
  14.  
This seems to work fine:
Expand|Select|Wrap|Line Numbers
  1. postgres=# select * from tf1(1);
  2.   pkg   |  comp
  3. --------+--------
  4.  Bronze | Humble
  5. (1 row)
  6.  
  7. postgres=# select * from tf1(4);
  8.    pkg    |   comp
  9. ----------+----------
  10.  Platinum | Humble
  11.  Platinum | Moderate
  12.  Platinum | Advanced
  13.  Platinum | Superior
  14. (4 rows)
  15.  
Hopefully you can use this example to make what you need.
May 6 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.