I need a function that does something like this pseudocode:

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

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.

  • You can use a CTE. Jun 10, 2014 at 21:13
  • @ypercube I'm almost there. I used a refcursor to execute my query and i could return it like RETURN QUERY FETCH ALL FROM curs1; but i cannot perform my UPDATE statement. Assuming curs1 is the refcursor with my data, how can I do something like UPDATE foo2 set status = 2 where id in(SELECT id from curs1);? I couldn't find this answer on the internet. Jun 10, 2014 at 22:42
  • I did not mean a cursor but a CTE: common table expression Jun 11, 2014 at 15:36

2 Answers 2


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 $$
    cur cursor for select id, other_column from my_table limit 20000;
    rec record;
    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 $$
    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 $$;
  • I just came to a solution pretty much like this, but what I was missing was exactly a way to execute it in only one update statement using IN(idValues). Can you help me with that? And another thing, I had an error RETURN NEXT cannot have a parameter in function with OUT parameters LINE 14: return next rec; Can you help me with that too? Thanks anyways... Jun 10, 2014 at 22:55
  • You have probably changed a return type of the function. It should be setof some_type. AFAIK "in (cursor)" solution is impossible.
    – klin
    Jun 10, 2014 at 23:02

Using a Common Table Expression (CTE), you can do this in one statement:

WITH myResultSet AS 
  ( SELECT id, some_other_column
    FROM ...

    ORDER BY ...something
    LIMIT 20000

, upd AS
  ( UPDATE some_other_table
    SET status = 2
    WHERE id IN (SELECT id FROM myResultSet)

FROM myResultSet ;

Tested at: SQL-Fiddle.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.