Re: enumerate groups given a certain value

Hello, Picavet.

> Anybody for a ray of light on a different approach ? This look like a
> recurrent problem, isn't there an experienced sql programmer here who
> tackled this issued a couple of time ?

Actually, I'm not very experienced in SQL. But from my point of view
this problem could be solved much more easily using plpgsql rather
than plain SQL.

Your initial query have been a little bit modified for convenience:
CREATE TABLE test_gen AS
SELECT *
FROM(
SELECT
chr((round(random()* 25) +65)::int) AS id,
random()* 100 AS val,
0::INTEGER AS gen
FROM
generate_series(1,200) as g
ORDER BY id
) foo;

CREATE OR REPLACE FUNCTION enum_groups (varchar) RETURNS SETOF test_gen AS
$body$
DECLARE
r chip.test_gen%ROWTYPE;
_id VARCHAR;
i INTEGER := 0;
q TEXT;
BEGIN
q := 'SELECT * FROM ' || $1 || ' ORDER BY id ASC, val ASC;';
FOR r IN EXECUTE q LOOP
IF ((_id IS NULL) OR (_id = r.id)) THEN
i := i + 1;
ELSE
i := 1;
END IF;
_id := r.id;
r.gen := i;
RETURN NEXT r;
END LOOP;
RETURN;
END;
$body$
LANGUAGE 'plpgsql';

Now you can SELECT * FROM enum_groups('test_gen') and you'll get following:

id val gen
B 2,35326588153839 1
B 11,4269167650491 2
B 11,9314394891262 3
B 27,9016905929893 4
B 28,548994101584 5
B 48,8151242025197 6
B 50,215089507401 7
B 59,613792411983 8
B 61,2281930632889 9
B 80,49540463835 10
C 5,86635880172253 1
C 11,5974457468838 2
C 15,8136531710625 3
C 29,8465201631188 4
C 52,9871591832489 5
C 57,3461000341922 6
C 63,3344274014235 7
...

HTH

PS. Sorry, I forget to reply all first time.
--
Best regards. Yuri.