更新表名为参数的游标的记录 [英] 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;
我调整了循环，并且voilà，循环遍历。现在在循环中的某一点，我需要更新记录（由光标指向），我陷入了困境。如何正确调整以下代码行？
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.
该记录？
（ii）该函数适用于公共模式中的表，并且对于来自其他模式（例如trace.myname）的表失败。
(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).
任何意见都非常感谢。
推荐答案
/ h2>
1。显式（未绑定）游标
EXECUTE 不是一个子句，而是一个PL / pgSQL函数来执行SQL字符串。光标在函数中不可见 。您需要将值传递给函数。
Answer for (i)
1. Explicit (unbound) cursor
EXECUTE is not a "clause", but a PL/pgSQL function to execute SQL strings. Cursors are not visible inside the function. You need to pass values to the function.
因此，您不能使用特殊语法 WHERE CURRENT OF cursor 。我使用系统列 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;
还有一个 FOR 语句循环播放游标，但只适用于 bound 光标。我们必须在这里使用一个未绑定的光标。
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 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类型
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:
- Table name as a PostgreSQL function parameter
这篇关于更新表名为参数的游标的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
