Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
You can use a CTE. –  ypercube Jun 10 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. –  Ivan De Sousa Paz Jun 10 at 22:42
    
I did not mean a cursor but a CTE: common table expression –  ypercube Jun 11 at 15:36

2 Answers 2

up vote 2 down vote accepted

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 $$;
share|improve this answer
    
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... –  Ivan De Sousa Paz Jun 10 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 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)
  ) 

SELECT *
FROM myResultSet ;

Tested at: SQL-Fiddle.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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