PostgreSQL function

Question

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.

Answer 1

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().

• Keith Nikkel: Sorry I took so long to notice your answer. Thanks for taking the time. What happens with your solution is that each of the components of a bill of materials gets the same bomitem_seqnumber. Each new bill of material gets the next higher integer corresponding to the number of the group. What I need is to have each bomitem_seqnumber incremented so that the first component in the bill has a bomitem_seqnumber of 10, the next 12, the next 14 etc. My function work great except that is not iterative across the entire bomitem table.
• John Smith: Hi can you maybe also help me with this question: stackoverflow.com/questions/44318816/… ? Thanks