postgresql - 函数内部的动态查询 postgres 语句有什么问题？

Question

我有一个将表名作为动态参数的 PL/pgSQL 函数。当我更新现有查询以将表名作为动态参数时，这就是我的函数:

DECLARE rec RECORD; 
BEGIN 
  EXECUTE 'insert into stat_300_8_0(ts, target, data)
               select distinct timestamp-(timestamp%3600) as wide_row_ts,
                      target, array[]::real[] as data
               from ' || temp_table_name || ' as temp 
               where class_id=8
                 and subclass_id=0 
                 and not exists (select ts from stat_300_8_0 
                                 where ts=temp.timestamp-(temp.timestamp%3600)
                                 and target=temp.target)'; 

  FOR rec IN EXECUTE 'SELECT DISTINCT timestamp AS ts 
                      FROM ' || temp_table_name ||
                    ' WHERE class_id=8' 
  LOOP 
      EXECUTE 'update stat_300_8_0 as disk_table
               set data[new_data.start_idx:new_data.end_idx] = array[data_0,data_1]
               from (select timestamp-(timestamp%3600) as wide_row_ts,
                            (timestamp%3600)/300 * 2 + 1 as start_idx,
                            ((timestamp%3600 / 300) + 1) * 2 as end_idx,
                            target, data_0, data_1
                     from ' || temp_table_name ||
                   ' where class_id=8 and subclass_id=0
                       and timestamp=rec.ts) as new_data
               where disk_table.ts=new_data.wide_row_ts
                 and disk_table.target=new_data.target';
  END LOOP; 
END;

然而，当这个函数被执行时，我得到一个错误提示

ERROR:  missing FROM-clause entry for table "rec"

但是，rec是在上面代码的第一行声明的。我无法弄清楚我的查询有什么问题。感谢您的帮助。

Answer 1

Eelke 回答的补充:

1. 假设 temp_table_name 是一个参数，您真的非常想通过 q​​uote_ident() 运行它，否则有人可以创建一个表，其名称可以将 sql 注入(inject)您的函数。

2. 您最好不要使用那里建议的更改，而是使用 EXECUTE...USING，因为这会为您提供有关值的参数化(从而防止 SQL 注入(inject))。您可以将 rec.ts 更改为 $1，然后添加到末尾 USING ts.rec(在引用的执行字符串之外)。这在您的 execute 中为您提供了一个更安全的参数化语句。但是，参数不能包含表名，因此它不能避免上面第一点。