更新表名作为参数的游标记录 [英] Update record of a cursor where the table name is a parameter

问题描述

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

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

declare
 pointCurs CURSOR FOR SELECT * from tableName for update;

用这个:

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;

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

I fixed the quotes for the tableName and pos and added the EXECUTE clause at the beginning, but I get the error on the where current of pointCurs.

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

任何评论都非常感谢..

Any comments are highly appreciated..

推荐答案

回答 (i)

1.显式(未绑定)游标

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

Answer for (i)

1. Explicit (unbound) cursor

EXECUTE is not a "clause", but a PL/pgSQL command to execute SQL strings. Cursors are not visible inside the command. You need to pass values to it.

因此，您不能使用特殊语法 WHERE当前光标.我使用系统列 ctid 而是在不知道唯一列的名称的情况下确定行.注意 ctid 只保证在同一个事务中是稳定的.

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

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;

为什么 format() 与 <代码>%I?

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

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

在 plpgsql 中通常不需要显式游标.改为使用 FOR 循环的隐式光标:

There is normally no need for explicit cursors in plpgsql. Use the implicit cursor of a FOR loop instead:

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 命令:

3. Set based approach

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

-- 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 个变体.

SQL Fiddle demonstrating all 3 variants.

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

A schema-qualified table name like trace.myname actually consists of two identifiers. You have to

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

• either pass and escape them separately,
• or go with the more elegant approach of using a 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 切换到 %s，因为 regclass 参数在(自动)转换为 时会自动正确转义文本.
此相关答案中的更多详细信息:

I switched from %I to %s, because the regclass parameter is automatically properly escaped when (automatically) converted to text.
More details in this related answer:

• 表名作为 PostgreSQL 函数参数

这篇关于更新表名作为参数的游标记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！