PostgresQL Stored Procs: Using a set of results in an update statement without looping through the set

Question

I have a stored procedure in which I need to query for a set of ids and then use that set in an UPDATE statement's WHERE clause. I'm using PostgresQL 9.0+. I'd rather not use a loop over the set of ids and issue multiple UPDATE statements -- that's not very efficient.

Here's a simple example:

CREATE OR REPLACE FUNCTION test_it()
RETURNS VOID AS $$
DECLARE
  cur_time  TIMESTAMP;
  ids       a%ROWTYPE;
BEGIN
  SELECT id FROM a INTO ids;
  UPDATE b
     SET state = 'foobar', updated_at = cur_time
   WHERE id IN ids;
END;
$$ LANGUAGE plpgsql;

This doesn't even compile.

I've also tried SELECT-ing the ids like so...

CREATE OR REPLACE FUNCTION test_it()
RETURNS VOID AS $$
DECLARE
  cur_time  TIMESTAMP;
  ids       a%ROWTYPE;
BEGIN
  SELECT id FROM a INTO ids;
  UPDATE b
     SET state = 'foobar', updated_at = cur_time
   WHERE id IN (SELECT ids);
END;
$$ LANGUAGE plpgsql;

This throws an error: HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

My actual stored proc is a lot more complicated because the initial query for the set of ids is actually a dynamic query.

The actual error output is this (just more context...):

ERROR:  operator does not exist: integer = task_responses
LINE 3:      WHERE id IN (SELECT task_response_ids)
                      ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
QUERY:  UPDATE task_responses
       SET state = state, updated_at = cur_time, rejected_at = cur_time
     WHERE id IN (SELECT task_response_ids)
CONTEXT:  PL/pgSQL function "reject_submissions_with_comment" line 38 at SQL statement

score 1 · Answer 1 · answered Jun 21 '12 at 18:38

At some point during the execution of my SP I needed to loop over results anyway, so I opted to keep track of the IDs I was visiting them and use them later in a dynamic query to do a single update.

FWIW: I could never get @Steve's suggestion to work because task_response_ids was a result set, not a table expression. If I embedded the query that would've worked, but then I would have needed to run the same query multiple times in my use case, because I had multiple updates (different tables) to do.

Here's the (fake) code based on my needs as outlined above and in the original question:

CREATE OR REPLACE FUNCTION test_it() RETURNS VOID AS $$
DECLARE
  cur_time    TIMESTAMP;
  state       varchar(20);
  a_response  RECORD;
  ids         bigint[];
  other_ids   bigint[];
  s_ids       varchar(4000);
  s_other_ids varchar(4000);
BEGIN
  state    := 'foobar';
  cur_time := CURRENT_TIMESTAMP;

  FOR a_response IN (SELECT id,other_id FROM a) LOOP
    ids[id_index]       := a_response.id;
    other_ids[id_index] := a_response.other_id;
    id_index            := id_index + 1;
    -- do other stuff with the current record
  END LOOP;
  s_ids       := array_to_string(ids, ',');
  s_other_ids := array_to_string(other_ids, ',');

  EXECUTE '
    UPDATE b
       SET state = $1, updated_at = $2
     WHERE id IN (' || s_ids || ')'
  USING state, cur_time;

  EXECUTE '
    UPDATE c
       SET state = $1, updated_at = $2
     WHERE id IN (' || s_other_ids || ')'
  USING state, cur_time;

END;
$$ LANGUAGE plpgsql;

This code is pretty fictitious, but it demonstrates the things I needed to accomplish.

score 0 · Answer 2 · answered Jun 19 '12 at 18:13

0

You want an UPDATE ... FROM ... style query, as per http://www.postgresql.org/docs/9.1/static/sql-update.html

UPDATE b
  SET state = 'foobar', updated_at = cur_time
FROM
  ids i
WHERE
  b.id = i.id;

answered Jun 19 '12 at 18:13

Steve

1,215
6
11

thanks, but that doesn't work. I get an error stating `ERROR: relation "ids" does not exist`. – jacaetevha Jun 19 '12 at 18:51
That code was assuming you had created your temporary table called ids as in your example code. I guess you could just do `from a where b.id = a.id`. – Steve Jun 19 '12 at 19:00
That still doesn't work. The `from_list` must be a list of table expressions. The result set is not a table expression. I've resorted to looping over the result set instead. Hmm.... – jacaetevha Jun 19 '12 at 19:56
The name of a table is a table expression. – Steve Jun 19 '12 at 20:15
I get what you're saying, but the result set is not a table name. I'm toying with cursors now, I'll update the question wehn I find a solution. – jacaetevha Jun 19 '12 at 23:42
Ha, I've just seen what's wrong with your code, I didn't look at it closely enough. `WHERE id IN (SELECT task_response_ids)` should be `WHERE id IN (SELECT id FROM task_response_ids)` assuming id is the right column name in task_response_ids. Postgres is being really nice to you and letting you treat a whole table as a value, unfortunately that table is not of type int (because it's a table). – Steve Jun 20 '12 at 00:26

PostgresQL Stored Procs: Using a set of results in an update statement without looping through the set

2 Answers2