PostgreSQL 函数
我创建了此函数来对 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 表重新排序。
I've created this function to re-sequence the sequence number on a BOM table (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;
The example works on an individual bomitem_parent_item_id
like below:
SELECT * from seqincr(14917);
I would like to rewrite this function to loop through
SELECT distinct bomitem_parent_item_id FROM bomitem;
so that it resequences the entire BOM table.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
使用 CTE 要做的事情要简单得多:
您至少需要 PostgreSQL 9.1 数据修改 CTE。
或者使用子查询,也适用于早期版本:
但是您至少需要 PostgreSQL 8.4 来实现 窗口函数
row_number()
。What you are trying to do is much simpler with a CTE:
You need at least PostgreSQL 9.1 for data-modifying CTE.
Or use a subquery, works in earlier versions, too:
But you need at least PostgreSQL 8.4 for the window function
row_number()
.