PostgreSQL >= 9.2 中如何从变量中选择表名

Question

我有一个变量是表名，请问如何在查询中使用这个变量进行选择或更新操作？例如：

create or replace function pg_temp.testtst ()
returns varchar(255) as 
$$
declare 
r record; t_name name;
begin   
  for r in SELECT tablename FROM pg_tables WHERE schemaname = 'public' limit 100 loop
      t_name = r.tablename; 
      update  t_name set id = 10 where id = 15; 
  end loop; 
  return seq_name;
end;
$$
language plpgsql; 

出现了错误:关系"t_name"不存在

- iLyas

---

尝试将tname作为表名。 - Sachin Verma

据我对PostgreSQL的记忆，你有更好的机会使其评估动态生成的SQL（例如执行 'update '+t_name+' set id = 10 where id = 15'）。请查阅PREPARE和EXECUTE语句。 - Anton Kovalenko

我想这应该是T-SQL语法。 - iLyas

Answer 1

正确的回答是Anton Kovalenko的评论。

在嵌入式SQL中，您不能将变量用作表名或列名。

UPDATE dynamic_table_name SET ....

PostgreSQL使用预处理和保存的计划来嵌入SQL，并且对目标对象（表）的引用在计划中被深度和硬编码 - 某些特征对计划具有重要影响，例如一个表可以使用索引，而另一个表则不行。查询规划相对较慢，因此PostgreSQL不会在透明的情况下尝试它（除了少数例外）。

您应该使用动态SQL - 其一个目的是用于类似情况的情形。您总是生成新的SQL字符串并且不保存计划。

DO $$
DECLARE r record;
BEGIN
  FOR r IN SELECT table_name 
              FROM information_schema.tables
             WHERE table_catalog = 'public'
  LOOP
    EXECUTE format('UPDATE %I SET id = 10 WHERE id = 15', r.table_name);
  END LOOP;
END $$;

注意：如果不对参数进行净化处理，动态 SQL 是不安全的（存在 SQL 注入风险）。我使用了函数“format”来实现。另外一种方法是使用“quote_ident”函数。

EXECUTE 'UPDATE ' || quote_ident(r.table_name) || 'SET ...

- Pavel Stehule