Skip site navigation (1) Skip section navigation (2)

Re: enumerate groups given a certain value

From: "Yura Gal" <yuragal(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: enumerate groups given a certain value
Date: 2008-08-07 21:37:41
Message-ID: 3b6c69d80808071437o1e644a12u9dfd51c3fb7e42a7@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
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.

In response to

pgsql-sql by date

Next:From: felixDate: 2008-08-07 21:39:27
Subject: Re: DELETE with JOIN
Previous:From: felixDate: 2008-08-07 21:35:46
Subject: Re: DELETE with JOIN

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group