我创建了这个函数来重新排列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/

10-14 18:26
查看更多