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.

Erwin Brandstetter · Accepted Answer · 2017-06-02 01:56:31Z

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

edited Jun 2, 2017 at 1:56

answered Feb 8, 2012 at 17:44

Erwin Brandstetter

668k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Keith Nikkel Over a year ago

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 Over a year ago

Hi can you maybe also help me with this question: stackoverflow.com/questions/44318816/… ? Thanks

Collectives™ on Stack Overflow

PostgreSQL function

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related