How to use UPDATE in PostgreSQL with variable table?

Question

Example:

Table=["category1","category2","category3"]

for varTable in Table:
    cr.execute('update varTable SET id=%s,WHERE id=%s)
     ........
       ....

How to do this loop?

Uroscopy

Answer 1

Use dynamic SQL for that. The default is to use plpgsql with EXECUTE.
Create a function or use a DO statement for ad-hoc execution.

Dynamic SQL

CREATE OR REPLACE FUNCTION f_up(_new_id int, _old_id int)
  RETURNS void AS
$BODY$
DECLARE
    _tbl text[] := '{category1,category2,category3}';
    t    text;

BEGIN

FOREACH t IN ARRAY _tbl
LOOP
    EXECUTE '
    UPDATE ' || t || '
    SET    id = $1
    WHERE  id = $2'
    USING  _new_id, _old_id;
END LOOP;

END;
$BODY$ LANGUAGE plpgsql;

Call:

SELECT f_up(23, 45);

There are lots of similar answers on SO. Search for dynamic-sql, plpgsql and EXECUTE for more examples and explanation.

Plain SQL

If plpgsql is still black magic to you, you can solve this simple case quite effectively with a data-modifying CTE. Requires PostgreSQL 9.1 - for data-modifying CTE.

WITH vals AS (
    SELECT 23 AS new_id, 45 AS old_id -- provide values once
    )
    , a AS (
    UPDATE category1
    SET    id = v.new_id
    FROM   vals v
    WHERE  id = v.old_id
    )
    , b AS (
    UPDATE category2
    SET    id = v.new_id
    FROM   vals v
    WHERE  id = v.old_id
    )
UPDATE category3
SET    id = v.new_id
FROM   vals v
WHERE  id = v.old_id;

Sylvanus

@John.zero: I added a plain SQL alternative. :) – Sylvanus

Plain SQL will be too long if I have lots of table to change.THK – Uroscopy

I just try Dynamic SQL in my Postgre SQL .there is an error"error: Operator is not Unique: unknown || text[] LINE 2: UPDATE '||_tbl||' ^" – Uroscopy

@John.zero: My bad, fixed the query. – Sylvanus

I need to add a variable in :CREATE OR REPLACE FUNCTION f_up(varTable,_new_id int, _old_id int),how to change the code? – Uroscopy

@John.zero: I suppose you start a new question where you describe your requirements and what you have. You can always link back to this one to make it simpler. Asking new questions in comments is discouraged on SO. – Sylvanus