如何从表名n Postgre> = 9.2的变量中选择 [英] How to select from variable that is a table name n Postgre >=9.2

问题描述

我有一个变量，它是表的名称.如何使用query中的变量从中进行选择或更新，例如:

i have a variable that is a name of a table. How can i select or update from this using variable in query , for example:

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"不存在

推荐答案

正确的回复是安东·科瓦连科(Anton Kovalenko)的评论

Correct reply is a comment from Anton Kovalenko

您永远不能在嵌入式SQL中将变量用作表名或列名.

You cannot use variable as table or column name in embedded SQL ever.

UPDATE dynamic_table_name SET ....

PostgreSQL对嵌入式SQL使用准备好的和保存的计划，并且对目标对象(表)的引用在计划中进行了深层和硬编码-一些特性对计划有重大影响-一个表可以使用索引，其他表可以使用索引不是.查询计划相对较慢，因此PostgreSQL不会透明地尝试它(没有少数例外).

PostgreSQL uses a prepared and saved plans for embedded SQL, and references to a target objects (tables) are deep and hard encoded in plans - a some characteristics has significant impact on plans - for one table can be used index, for other not. Query planning is relatively slow, so PostgreSQL doesn't try it transparently (without few exceptions).

您应该使用动态SQL -一种用途是用于类似情况.您总是会生成一个新的SQL字符串，并且计划不会保存

You should to use a dynamic SQL - a one purpose is using for similar situations. You generate a new SQL string always and plans are not saved

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 "功能.

Attention: Dynamic SQL is unsafe (there is a SQL injection risks) without parameter sanitization. I used a function "format" for it. Other way is using "quote_ident" function.

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

这篇关于如何从表名n Postgre> = 9.2的变量中选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！