我创建了这个函数来重新排列BOM表(bomitem)上的序列号。
CREATE OR REPLACE FUNCTION seqincr(integer)
RETURNS SETOF bomitem AS
$BODY$
DECLARE
pItemid ALIAS FOR $1;
_row bomitem%ROWTYPE;
seqint int;
_id int;
BEGIN
seqint=8;
FOR _row IN SELECT *
FROM bomitem
WHERE ((bomitem_parent_item_id=pItemid))
LOOP
RETURN NEXT _row;
_id = _row.bomitem_id;
seqint = seqint+2;
update bomitem set bomitem_seqnumber = seqint where bomitem_id=_id;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION seqincr(integer)
OWNER TO admin;
该示例对个人
bomitem_parent_item_id
起作用,如下所示:SELECT * from seqincr(14917);
我想重写此函数以循环通过
SELECT distinct bomitem_parent_item_id FROM bomitem;
以便重新排序整个BOM表。
最佳答案
你想用CTE做的事情要简单得多:
WITH x AS (
SELECT bomitem_parent_item_id
, row_number() OVER (ORDER BY bomitem_parent_item_id) AS rn
FROM bomitem
GROUP BY bomitem_parent_item_id
ORDER BY bomitem_parent_item_id
)
UPDATE bomitem b
SET bomitem_seqnumber = 8 + 2 * rn
FROM x
WHERE x.bomitem_parent_item_id = b.bomitem_id;
对于data-modifying CTE,您至少需要PostgreSQL 9.1。
或者使用子查询,也适用于早期版本:
UPDATE bomitem b
SET bomitem_seqnumber = 8 + 2 * rn
FROM (
SELECT bomitem_parent_item_id
, row_number() OVER (ORDER BY bomitem_parent_item_id) AS rn
FROM bomitem
GROUP BY bomitem_parent_item_id
ORDER BY bomitem_parent_item_id
) x
WHERE x.bomitem_parent_item_id = b.bomitem_id;
但对于window function
row_number()
,您至少需要PostgreSQL 8.4。关于postgresql - PostgreSQL功能,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/9197399/