You are trying to rely on the order of rows to define groups -- which is explicitly forbidden by relational systems. You need to reform your schema to account for what groups are, and input your data accordingly. There is no window function in existence that bases its output on the order of rows, because the order of a result is not considered a fact by itself and therefore can't be taken into account by a function.
If you already have a bad schema but it has data in it already, you can write a procedural function that migrates your data to a better schema. The reason it can work (but is extremely fragile
) is because it is not relational (declarative), it is procedural (step-by-step).
Here is one way. Save this as a script and run it from psql:
- sql Code
CREATE TABLE stupid
(id integer NOT NULL,
flag integer NOT NULL);
INSERT INTO stupid
( 1, 0),
( 2, 0),
( 3, 1),
( 4, 1),
( 5, 0),
( 6, 1),
( 7, 0),
( 8, 0),
( 9, 1),
CREATE TABLE smart
(id SERIAL PRIMARY KEY);
CREATE TABLE groups
(id SERIAL PRIMARY KEY);
CREATE TABLE members
(member integer REFERENCES smart ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
gid integer REFERENCES groups ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
PRIMARY KEY (member, gid));
CREATE FUNCTION fix_stupid() RETURNS VOID AS
last_flag integer := 0;
FOR x IN (SELECT id, flag FROM stupid ORDER BY id) LOOP
EXECUTE $q$INSERT INTO smart VALUES ($1)$q$ USING x.id;
IF x.flag > 0 THEN
IF last_flag < 1 THEN
INSERT INTO groups VALUES (DEFAULT) RETURNING id INTO current_gid;
EXECUTE $q$INSERT INTO members (member, gid) VALUES ($1, $2)$q$
USING x.id, current_gid;
last_flag := x.flag;
SELECT MAX(id) INTO new_pk FROM smart;
RAISE NOTICE 'Setting smart_id_seq to %', new_pk;
PERFORM SETVAL('smart_id_seq', new_pk);
$$ LANGUAGE plpgsql;
SELECT s.id, m.gid
FROM smart AS s LEFT OUTER JOIN members AS m ON (s.id = m.member)
ORDER BY id;
From within psql the result is:
ceverett=# \i stupid.sql
INSERT 0 11
psql:stupid.sql:21: NOTICE: CREATE TABLE will create implicit sequence "smart_id_seq" for serial column "smart.id"
psql:stupid.sql:21: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "smart_pkey" for table "smart"
psql:stupid.sql:24: NOTICE: CREATE TABLE will create implicit sequence "groups_id_seq" for serial column "groups.id"
psql:stupid.sql:24: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "groups_pkey" for table "groups"
psql:stupid.sql:29: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "members_pkey" for table "members"
psql:stupid.sql:56: NOTICE: Setting smart_id_seq to 11
id | gid
3 | 1
4 | 1
6 | 2
9 | 3
10 | 3
11 | 3
Junk like that is always eventually required when you initially decide to create a braindead schema. Keep that in mind when working on real projects. Schema changes are a primary killer, because data is central to everything that you do in programming.