CODEWITHSUNDEEP
postgresqlstored-proceduresplpgsqlreturn-typepostgresql-8.4
Alexander Farber
Alexander Farber

Reputation: 22988

How to create PL/pgSQL function returning several rows

I'm trying to create a PL/pgSQL function, which should populate a temporary table and then return all rows from it (it will be a join later), but I don't know which return type 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 statements above work on their own, but give me the 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 because I have wrongly specified the returns void above, but I don't know which return type to use instead and omitting the return type is a compile error.

Upvotes: 4

Views: 2563

Answers (1)

mu is too short
mu is too short

Reputation: 434665

You want to use a setof varchar return type and then return query ... inside the function. From the fine manual:

39.6.1.2. RETURN NEXT and RETURN QUERY

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

When a PL/pgSQL function is declared to return SETOF sometype, the procedure to follow is slightly different. In that case, the individual items to return are specified by a sequence of RETURN NEXT or RETURN QUERY commands, and then a final RETURN command with no argument is used to indicate that the function has finished executing.

I think you want something more like this:

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 pointless here:

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

So PostgreSQL is computing the entire result set and caching it by itself. You could do this:

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'm pretty sure the temp table is going to be dropped at the end of your function anyway so you should get rid of it.

Upvotes: 5

Related Questions