我正在调整一些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
子句,但在上出现了错误where current of pointCurs
。
(i)如何更新记录?
(ii)该功能对于公共模式中的表正常运行,而对于其他模式中的表(例如trace.myname)失败。
任何评论都受到高度赞赏。
EXECUTE
不是“子句”,而是执行SQL字符串的PL / pgSQL命令。游标在命令内部不可见。您需要将值传递给它。
因此,您不能使用特殊语法WHERE CURRENT OF
cursor
。我改用系统列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;
该FOR
语句还有一个变体可以遍历游标,但是它仅适用于绑定的游标。我们必须在这里使用一个未绑定的游标。
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;
更好的方法是(如果可能的话!):从基于集合的操作的角度重新考虑您的问题,并执行一个(动态)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个变体。
类似模式的表名trace.myname
实际上由两个标识符组成。你必须
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
参数在(自动)转换为时会自动正确转义text
。
此相关答案中的更多详细信息:
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句