Return SETOF rows from PostgreSQL function after WHILE LOOP section

Question

I am trying to create a POSTGRESQL function which would first INSERT some data in a table using WHILE LOOP and then SELECT the results of this table.

This is an sql example:

CREATE OR REPLACE FUNCTION get_levels_test (maxlevel int) RETURNS SETOF list_of_levels  AS $$
DECLARE
    level int = 1;
BEGIN
 TRUNCATE list_of_levels;

 WHILE (level <= maxlevel) LOOP
    INSERT INTO list_of_levels
        SELECT level;             

    level = level + 1;
 END LOOP;
    select * from list_of_levels;
END;
$$ LANGUAGE PLPGSQL VOLATILE;

Then i try to call this function with: select get_levels_test (3), which shows me this error:

ERROR: query has no destination for result data
Hint: If you want to discard the results of a SELECT, use PERFORM instead.
Where: PL/pgSQL function "get_levels_test" line 12 at SQL statement

The list_of_levels table contains just an int column.

In case this is needed, I am using PostgreSQL 8.2.15 (Greenplum Database 4.3.3.1 build 1).

Answer 1

You need to use "return next" which can be used with a composite type or with a table. Please use this feature with caution as you can have performance issues with using this. Do not use this to join the results to another table. Do not return large datasets with this. Only use it for very small results such as a short report.

You also need to add exception handling into your function. I added this to my example function below.

Example table:

    create table employees
    (id int not null,
     manager_id int null,
     employee_title text not null)
     distributed by (id);

Example data:

    insert into employees values
    (1, null, 'President'),
    (2, 1, 'Vice-President'),
    (3, 2, 'Manager'),
    (4, 3, 'Engineer'),
    (5, null, 'CMO'),
    (6, 5, 'Director'),
    (7, 6, 'Assistant'),
    (8, 7, 'Developer');

Function:

    create or replace function get_employees(p_id int) returns setof employees as
    $$
    declare
            v_function_name text := 'get_employees';
            v_location int;
            v_rec record;
            v_rec2 employees;
            v_id employees.id%type;
    begin
            v_location := 1000;
            create temporary table t1
            (id integer) on commit drop distributed by (id);

            v_location := 2000;
            for v_rec in (select id from employees where id = p_id and manager_id is null order by id) loop
                    v_id := v_rec.id;
                    insert into t1 values (v_id);
                    while v_id is not null loop
                            select id into v_id from employees where manager_id = v_id;
                            if v_id is not null then
                                    insert into t1 values (v_id);
                            end if;
                    end loop;
            end loop;

            v_location := 3000;
            for v_rec2 in (select * from employees e join t1 on e.id = t1.id order by e.id) loop
                    return next v_rec2;
            end loop;
    exception
      when others then
        raise exception '(%:%:%)', v_function_name, v_location, sqlerrm;
    end;
    $$
    language plpgsql;

And using the function:

    select * From get_employees(1);
     id | manager_id | employee_title 
    ----+------------+----------------
      1 |            | President
      2 |          1 | Vice-President
      3 |          2 | Manager
      4 |          3 | Engineer
    (4 rows)


    select * From get_employees(5);
     id | manager_id | employee_title 
    ----+------------+----------------
      5 |            | CMO
      6 |          5 | Director
      7 |          6 | Assistant
      8 |          7 | Developer
    (4 rows)