How to create a PL / pgSQL function that returns multiple rows

Question

I am trying to create a PL / pgSQL function that should populate a temporary table and then return all rows from it (it will be joined later), but I do not know what type of return to specify for it:

create or replace function pref_daily_misere() returns void as $BODY$ begin create temporary table temp_best (id varchar not null) on commit drop; insert into temp_best (id) select id from pref_money where yw = to_char(current_timestamp - interval '1 week', 'IYYY-IW') order by money desc limit 10; select id from temp_best; end; $BODY$ language plpgsql; 

The above statements work on their own but give me an error

 # select pref_daily_misere(); ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT: PL/pgSQL function "pref_daily_misere" line 7 at SQL statement 

when I try to call it in my PostgreSQL 8.4.11 database.

This is probably due to the fact that I incorrectly indicated the void returns above, but I do not know what type of return value to use instead, and omitting the return type is a compilation error.

Answer 1

You want to use the setof varchar return type, and then return query ... inside the function. From the exact guide :

39.6.1.2. RETURN NEXT and RETURN QUERY

 RETURN NEXT expression; RETURN QUERY query; RETURN QUERY EXECUTE command-string [ USING expression [, ... ] ]; 

When the PL / pgSQL function is declared to return SETOF sometype , the procedure to follow is slightly different. In this case, individual returned items are indicated by a sequence of RETURN NEXT or RETURN QUERY commands, and then the last RETURN command without an argument is used to indicate that the function has completed execution.

I think you need something more:

 create or replace function pref_daily_misere() returns setof varchar as $BODY$ begin create temporary table temp_best (id varchar not null) on commit drop; insert into temp_best (id) select id from pref_money where yw = to_char(current_timestamp - interval '1 week', 'IYYY-IW') order by money desc limit 10; return query select id from temp_best; return; end; $BODY$ language plpgsql; 

However, the temp table is useless here:

Note. The current implementation of RETURN NEXT and RETURN QUERY saves the entire set of results before returning from the function, as discussed above.

So, PostgreSQL computes the entire result set and caches it by itself. You can do it:

 create or replace function pref_daily_misere() returns setof varchar as $BODY$ begin return query select id from pref_money where yw = to_char(current_timestamp - interval '1 week', 'IYYY-IW') order by money desc limit 10; return; end; $BODY$ language plpgsql; 

I am sure that the temporary table will still be reset at the end of your function, so you should get rid of it.