更新游标的记录，其中表名是参数

我正在调整一些 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 and pos并添加了EXECUTE条款在开头，但我收到错误where current of pointCurs.

问题：

1. 我怎样才能更新记录？
2. 该函数对于公共模式中的表可以正常工作，而对于其他模式中的表（例如，trace.myname）则失败。

任何评论都将受到高度赞赏..

回答（一）

1. 显式（未绑定）游标

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循环游标的语句，但它只适用于bound光标。我们必须在这里使用未绑定的游标。

2. 隐式光标进入FOR loop

有通常不需要对于 plpgsql 中的显式游标。使用 a 的隐式游标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小提琴演示所有 3 个变体。

回答（二）

模式限定的表名，例如trace.myname实际上包括two身份标识。你必须

• 要么通过并逃离他们分别地,
• 或与更优雅的方法使用一个regclass type:

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

我从%I to %s，因为regclass当（自动）转换为时参数会自动正确转义text.
此相关答案中的更多详细信息：

• 表名作为 PostgreSQL 函数参数