如何在Postgresql函数中使用临时序列

Question

我有一些SQL行，它将从相同的GROUP_ID中获取一组不连续的id(例如，如果某些行被删除)，并将使它们再次连续。我想把它变成一个可重用的函数。如果单独执行，行可以工作但当我试图创建函数时，我得到错误

ERROR:  relation "id_seq_temp" does not exist  
LINE 10: UPDATE THINGS SET ID=nextval('id_se...

如果我在函数外部创建一个序列，并在函数中使用该序列，则该函数成功创建(模式限定或不限定)。然而，我觉得在函数内部创建temp序列而不是将其留在模式中是一个更干净的解决方案。

我已经看到了这个问题:函数显示错误"关系my_table不存在">
然而，我正在使用公共模式和模式限定序列与public.似乎没有帮助。

我也看到过这个问题:如何在PostgreSQL8上使用临时序列和SELECT创建sql函数。我可能可以使用generate_series，但这增加了SERIES解决的许多复杂性，例如需要知道生成的序列有多大。

这是我的功能，我匿名了一些名字-以防出现打字错误。

CREATE OR REPLACE FUNCTION reindex_ids(IN BIGINT) RETURNS VOID
LANGUAGE SQL
AS $$
CREATE TEMPORARY SEQUENCE id_seq_temp
MINVALUE 1
START WITH 1
INCREMENT BY 1;
ALTER SEQUENCE id_seq_temp RESTART;
UPDATE THINGS SET ID=ID+2000 WHERE GROUP_ID=$1;
UPDATE THINGS SET ID=nextval('id_seq_temp') WHERE GROUP_ID=$1;
$$;

是否可以在函数中稍后使用您在函数中创建的序列?

Answer 1

问题答案

原因是SQL函数(LANGUAGE sql)按1进行解析和规划。在函数运行之前，使用的所有对象必须存在。

可以切换到PL/pgSQL， (LANGUAGE plpgsql)，它按需规划每个语句。在这里，您可以创建对象并在下一个命令中使用它们。

:

• 为什么PL/pgSQL函数有副作用，而SQL函数没有?

由于您没有返回任何内容，请考虑PROCEDURE。(FUNCTION也可以)

CREATE OR REPLACE PROCEDURE reindex_ids(IN bigint)
LANGUAGE plpgsql AS
$proc$
BEGIN
IF EXISTS ( SELECT FROM pg_catalog.pg_class
WHERE  relname = 'id_seq_temp'
AND    relnamespace = pg_my_temp_schema()
AND    relkind = 'S') THEN
ALTER SEQUENCE id_seq_temp RESTART;
ELSE
CREATE TEMP SEQUENCE id_seq_temp;
END IF;
UPDATE things SET id = id + 2000 WHERE group_id = $1;
UPDATE things SET id = nextval('id_seq_temp') WHERE group_id = $1;
END
$proc$;

调用:

CALL reindex_ids(123);

如果您的临时序列不存在，则创建该序列。
如果该序列存在，则重置。(请记住，临时对象在会话期间存在。)
如果其他对象占用了该名称，则会引发异常。

替代解决方案

解决方案1

这通常有效:

UPDATE things t
SET    id = t1.new_id
FROM  (
SELECT pk_id, row_number() OVER (ORDER BY id) AS new_id
FROM   things
WHERE  group_id = $1     -- your input here
) t1
WHERE  t.pk_id = t1.pk_id;

并且只更新每一行一次，所以成本减半。

将pk_id替换为PRIMARY KEY列，或任何UNIQUE NOT NULL(组合)列。

技巧在于UPDATE通常根据FROM子句中子查询的排序顺序处理行。按升序更新永远不会碰到重复键冲突。
窗口函数row_number()的ORDER BY子句对结果集施加了这个排序顺序。这是一个未记录的实现细节，因此您可能希望向子查询添加显式的ORDER BY。但是由于UPDATE的行为没有文档记录，它仍然取决于实现细节。

你可以把那个包装成一个普通的SQL函数。

解决方案2

不考虑做你正在做的事。序列号上的差距通常是意料之中的，不是问题。接受现实吧。看到:

• 复合键的每组行序列号