Re: Update with a Repeating Sequence - Mailing list pgsql-general

Steve Atkins wrote:
>
> On Oct 14, 2008, at 9:04 AM, Bill Thoen wrote:
>
>> I've got a table with repeated records that I want to make unique by
>> adding a sequence code of 0,1,2,...,n for each set of repeated
>> records. Basically, I want to turn:
>> field_id | seq
>> ----------+-----
>>       1 |   0
>>       2 |   0
>>       3 |   0
>>       3 |   0
>>       3 |   0
>>       4 |   0
>>       4 |   0
>>       5 |   0
>>       6 |   0
>> into:
>> field_id | seq
>> ----------+-----
>>       1 |   0
>>       2 |   0
>>       3 |   0
>>       3 |   1
>>       3 |   2
>>       4 |   0
>>       4 |   1
>>       5 |   0
>>       6 |   0
>>
>> What's the best way to that?
>
> This is mildly tricky to do, and hard to maintain.
>
> In most cases where people say they need this, they're actually
> perfectly happy with the seq value being enough to make the row
> unique, and ideally increasing in order of something such as insertion
> time ...
Thanks, but in this case I really need both unique records and a
repeated sequence so I can select the first occurrence of each record
(i.e. WHERE seq =0) and sometimes I need the max(seq) for particular
records. Since this is a read-only table, maintaining it is not a
problem.  Anyway, I did mange to solve it. Here's a little test script
that shows how:

CREATE TABLE test (
   field_id integer,
   seq integer
);
INSERT INTO test VALUES (1, 0);
INSERT INTO test VALUES (2, 0);
INSERT INTO test VALUES (3, 0);
INSERT INTO test VALUES (3, 0);
INSERT INTO test VALUES (3, 0);
INSERT INTO test VALUES (4, 0);
INSERT INTO test VALUES (4, 0);
INSERT INTO test VALUES (5, 0);
INSERT INTO test VALUES (6, 0);

-- Create table to hold static variables
CREATE TABLE tmp (last_id integer, cnt integer);
INSERT INTO tmp VALUES(0,0);

-- Function to fill in repeated sequence
CREATE OR REPLACE FUNCTION test_it (field_id integer) RETURNS integer AS $$
DECLARE
   r tmp%ROWTYPE;
   nLast_id integer;
   nCnt integer;
   BEGIN
      SELECT * INTO r FROM tmp;
      nLast_id = r.last_id;
      nCnt = r.cnt;
      IF field_id = nLast_id THEN
         nCnt = nCnt + 1;
      ELSE
         nCnt = 0;
         nLast_id = field_id;
      END IF;
      UPDATE tmp SET last_id=nLast_id, cnt=nCnt;
      RETURN nCnt;
   END;
$$ LANGUAGE plpgsql;

-- Fill in repeated sequence
UPDATE test SET seq=test_it (field_id);

-- Show results
SELECT * FROM test ORDER BY field_id, seq;

-- Clean up
DROP FUNCTION test_it(integer);
DROP TABLE tmp;
DROP TABLE test;