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 genFROM
generate_series(1,200)as gORDER BY id
) foo;
CREATE OR REPLACE FUNCTION enum_groups (varchar) RETURNS SETOF test_gen AS
$body$
DECLAREr chip.test_gen%ROWTYPE;_id VARCHAR;i INTEGER := 0;q TEXT;
BEGINq := '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.
