Re: Update with a Repeating Sequence

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;