postgresql - 以表名为参数的游标更新记录

Question

我正在调整一些 PL/pgSQL 代码，以便我的 refcursor 可以将表名作为参数。因此，我更改了以下行:

declare
 pointCurs CURSOR FOR SELECT * from tableName for update;

用这个:

OPEN pointCurs FOR execute 'SELECT * FROM ' || quote_ident(tableName) for update;

我调整了循环，瞧，循环通过了。现在在循环中的某个点我需要更新记录(由光标指向)并且我被卡住了。我应该如何正确调整以下代码行？

UPDATE tableName set tp_id = pos where current of pointCurs;

我修复了 tableName 和 pos 的引号，并在开头添加了 EXECUTE 子句，但我在 pointCurs 的当前位置。

问题:

1. 如何更新记录？
2. 该函数对来自公共(public)模式的表正常工作，但对来自其他模式(例如 trace.myname)的表失败。

非常感谢任何评论..

Answer 1

(i) 的答案

1。显式(未绑定(bind))游标

EXECUTE不是“子句”，而是执行 SQL 字符串的 PL/pgSQL 命令。光标在命令中不可见。您需要将值传递给它。

因此，您不能使用特殊语法 WHERE CURRENT OFcursor .我用系统栏ctid而是在不知道唯一列名称的情况下确定行。请注意，ctid 仅保证在同一事务内保持稳定。

CREATE OR REPLACE FUNCTION f_curs1(_tbl text)
  RETURNS void AS
$func$
DECLARE
   _curs refcursor;
   rec record;
BEGIN
   OPEN _curs FOR EXECUTE 'SELECT * FROM ' || quote_ident(_tbl) FOR UPDATE;

   LOOP
      FETCH NEXT FROM _curs INTO rec;
      EXIT WHEN rec IS NULL;

      RAISE NOTICE '%', rec.tbl_id;

      EXECUTE format('UPDATE %I SET tbl_id = tbl_id + 10 WHERE ctid = $1', _tbl)
      USING rec.ctid;
   END LOOP;
END
$func$  LANGUAGE plpgsql;

Why format() with %I?

还有一个变体 FOR statement to loop through cursors , 但它只适用于绑定(bind) 游标。我们必须在这里使用未绑定(bind)的游标。

2。 FOR 循环中的隐式游标

plpgsql 中通常不需要显式游标。使用 FOR 循环的隐式游标代替:

CREATE OR REPLACE FUNCTION f_curs2(_tbl text)
  RETURNS void AS
$func$
DECLARE
   _ctid tid;
BEGIN
   FOR _ctid IN EXECUTE 'SELECT ctid FROM ' || quote_ident(_tbl) FOR UPDATE
   LOOP
      EXECUTE format('UPDATE %I SET tbl_id = tbl_id + 100 WHERE ctid = $1', _tbl)
      USING _ctid;
   END LOOP;
END
$func$  LANGUAGE plpgsql;

3。基于集合的方法

或者更好，但(如果可能的话!):根据基于集合的操作重新考虑您的问题并执行单个(动态)SQL 命令:

-- Set-base dynamic SQL
CREATE OR REPLACE FUNCTION f_nocurs(_tbl text)
  RETURNS void AS
$func$
BEGIN
   EXECUTE format('UPDATE %I SET tbl_id = tbl_id + 1000', _tbl);
   -- add WHERE clause as needed
END
$func$  LANGUAGE plpgsql;

SQL Fiddle演示所有 3 个变体。

(ii) 的答案

像 trace.myname 这样的模式限定表名实际上由两个 标识符组成。你必须

• 分别通过和转义它们，
• 或者采用 更优雅的方法 使用 regclass 类型:

CREATE OR REPLACE FUNCTION f_nocurs(_tbl <b>regclass</b>)
  RETURNS void AS
$func$
BEGIN
   EXECUTE format('UPDATE <b>%s</b> SET tbl_id = tbl_id + 1000', _tbl);
END
$func$  LANGUAGE plpgsql;

我从 %I 切换到 %s，因为 regclass 参数在(自动)转换为 时自动正确转义文本.
此相关答案中的更多详细信息:

• Table name as a PostgreSQL function parameter