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

Update record of a cursor where the table name is a parameter

提问by arthur

I am adjusting some PL/pgSQL code so my refcursorcan take the table name as parameter. Therefore I changed the following line:

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

declare
 pointCurs CURSOR FOR SELECT * from tableName for update;

with this one:

有了这个：

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

I adjusted the loop, and voilà, the loop went through. Now at some point in the loop I needed to update the record (pointed by the cursor) and I got stuck. How should I properly adjust the following line of code?

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

UPDATE tableName set tp_id = pos where current of pointCurs;

I fixed the quotes for the tableNameand posand added the EXECUTEclause at the beginning, but I get the error on the where current of pointCurs.

我修复了tableNameand的引号并在开头pos添加了EXECUTE子句，但是我在where current of pointCurs.

Questions:

问题：

(i) How can I update the record?
(ii) The function was working properly for tables from the public schema and failed for tables from other schemas (e.g., trace.myname).

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

Any comments are highly appreciated..

任何评论都非常感谢..

回答by Erwin Brandstetter

Answer for (i)

回答 (i)

1. Explicit (unbound) cursor

1. 显式（未绑定）游标

EXECUTEis not a "clause", but a PL/pgSQL command to execute SQL strings. Cursors are not visibleinside the command. You need to pass values to it.

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

Hence, you cannot use the special syntax WHERE CURRENT OFcursor. I use the system column ctidinstead to determine the row without knowing the name of a unique column. Note that ctidis only guaranteed to be stable within the same transaction.

因此，您不能使用特殊语法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 = ', _tbl)
      USING rec.ctid;
   END LOOP;
END
$func$  LANGUAGE plpgsql;

Why format()with %I?

为什么format()用%I？

There is also a variant of the FORstatement to loop through cursors, but it only works for boundcursors. We have to use an unbound cursor here.

还有一个FOR语句的变体来循环游标，但它只适用于绑定游标。我们必须在这里使用未绑定的游标。

2. Implicit cursor in FORloop

2.FOR循环中的隐式游标

There is normally no needfor explicit cursors in plpgsql. Use the implicit cursor of a FORloop instead:

有通常没有必要在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 = ', _tbl)
      USING _ctid;
   END LOOP;
END
$func$  LANGUAGE plpgsql;

3. Set based approach

3. 基于集合的方法

Or better, yet (if possible!): Rethink your problem in terms of set-based operations and execute a single (dynamic) SQL command:

或者更好（如果可能！）：根据基于集合的操作重新思考您的问题并执行单个（动态）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 Fiddledemonstrating all 3 variants.

SQL Fiddle演示了所有 3 个变体。

Answer for (ii)

对 (ii) 的回答

A schema-qualified table name like trace.mynameactually consists of twoidentifiers. You have to

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

• either pass and escape them separately,
• or go with the more elegant approachof using a regclasstype:

• 要么通过并逸出他们分开，
• 或者采用更优雅的使用regclass类型的方法：

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 switched from %Ito %s, because the regclassparameter is automatically properly escaped when (automatically) converted to text.
More details in this related answer:

我从 切换%I到%s，因为regclass当（自动）转换为text.
此相关答案中的更多详细信息：

• Table name as a PostgreSQL function parameter

• 表名作为 PostgreSQL 函数参数