How to create PL/pgSQL function returning several rows

Question

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.

Is there any reason to tuck the above stuff insde a function? A view (or CTE) seems perfectly suited, IMHO. (maybe except for the ugly limit) — wildplasser
– wildplasser, Commented Jun 10, 2012 at 22:34
It is not a complete function, I'll add more stuff later there. — Alexander Farber
– Alexander Farber, Commented Jun 11, 2012 at 11:32

mu is too short · Accepted Answer · 2012-06-10 22:29:22Z

5

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.

answered Jun 10, 2012 at 22:29

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alexander Farber Over a year ago

Thank you, but why is the second return necessary? I've commented it out and it seems to work ok.

mu is too short Over a year ago

@AlexanderFarber: The second return; isn't necessary but the docs say "a final RETURN command with no argument is used to indicate that the function has finished executing" so I put it in to avoid questions about why it wasn't there :)

Collectives™ on Stack Overflow

How to create PL/pgSQL function returning several rows

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related