PostgreSQL – Function to Execute Query, Manipulate and Return Result

functionspostgresqlpostgresql-9.2set-returning-functions

I need a function that does something like this pseudocode:

function get_data() RETURN SET OF something... as
BEGIN
    myResultSet = select id, some_other_column from ...... limit 20000;
    update some_other_table set status = 2 where id in (myResultSet.id);
    RETURN QUERY myResultSet;
END;

In other words, I need to execute a query, use the IDs I got in other update statement and then return this same query result.

I was wondering if there is a way to store a resultset in some kind of variable, but my research didn't find any good results. Any other kind of solution is appreciated, since I don't need to execute my same query twice.

Best Answer

You can use a cursor.

create type my_type as (id int, other_column text);

create or replace function get_data ()
returns setof my_type
language plpgsql as $$
declare
    cur cursor for select id, other_column from my_table limit 20000;
    rec record;
begin
    for rec in cur loop
        update other_table
            set status = 2 
            where id = rec.id;
        return next rec;
    end loop;
end $$;

select * from get_data();

Note however that the function will perform 20000 (maybe less) updates, not just one. It is not obvious if performance will be better or worse than in one query, as it depends on various circumstances. For example, one query should be much faster on an idle server whereas the function might turn out to be better on a heavily loaded server. The best way to check it is to test the function in the actual environment.

You can also use a temporary table. In this case rows are updated in one query.

create or replace function get_data_2 ()
returns setof my_type
language plpgsql as $$
begin
    create temporary table temp_table of my_type on commit drop;
    insert into temp_table
        select id, other_column from my_table limit 20000;
    update other_table 
        set status = 2
        where id in (select id from temp_table);
    return query select * from temp_table;
end $$;
Related Question