up vote 0 down vote favorite
share [g+] share [fb]

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.

link|improve this question
feedback

1 Answer

up vote 1 down vote

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.

In earlier versions, this should work:

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
    ) AS x
WHERE  x.bomitem_parent_item_id = b.bomitem_id;

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

As an aside: In plpgsql the assignment operator is :=. Use of = is undocumented.

link|improve this answer
feedback

Your Answer

 
or
required, but never shown

Not the answer you're looking for? Browse other questions tagged or ask your own question.