PostgreSQL 函数

发布于 2025-01-03 21:30:53 字数 880 浏览 0 评论 0原文

我创建了此函数来对 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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

倒带 2025-01-10 21:30:53

使用 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;

您至少需要 PostgreSQL 9.1 数据修改 CTE

或者使用子查询,也适用于早期版本:

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;

但是您至少需要 PostgreSQL 8.4 来实现 窗口函数 row_number()

What you are trying to do is much simpler with a 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;

You need at least PostgreSQL 9.1 for data-modifying CTE.

Or use a subquery, works in earlier versions, too:

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;

But you need at least PostgreSQL 8.4 for the window function row_number().

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文