如何在带有变量表的PostgreSQL中使用UPDATE？ [英] How to use UPDATE in PostgreSQL with variable table?

问题描述

示例：

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

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

如何执行此循环？

推荐答案

为此使用动态SQL。默认设置是将plpgsql与 EXECUTE 。

创建函数或使用 DO 语句进行临时执行。

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.

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;

致电：

SELECT f_up(23, 45);

SO有很多类似的答案。搜索 dynamic-sql ，< a href = / questions / tagged / plpgsql class = post-tag title =显示标记为'plpgsql' rel = tag> plpgsql 和 EXECUTE 的问题c $ c>了解更多示例和解释。

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

如果plpgsql对您来说仍然是黑魔法，您可以使用修改数据的CTE非常有效地解决这种简单情况。需要PostgreSQL 9.1-数据修改CTE 。

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;

这篇关于如何在带有变量表的PostgreSQL中使用UPDATE？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！