Example:
Table=["category1","category2","category3"]
for varTable in Table:
cr.execute('update varTable SET id=%s,WHERE id=%s)
........
....
How to do this loop?
Add a comment
|
1 Answer
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;
answered Aug 2, 2012 at 1:28
Erwin Brandstetter
669k160 gold badges1.2k silver badges1.3k bronze badges
6 Comments
Erwin Brandstetter
@John.zero: I added a plain SQL alternative. :)
John.zero
Plain SQL will be too long if I have lots of table to change.THK
John.zero
I just try Dynamic SQL in my Postgre SQL .there is an error"error: Operator is not Unique: unknown || text[] LINE 2: UPDATE '||_tbl||' ^"
Erwin Brandstetter
@John.zero: My bad, fixed the query.
John.zero
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?
|