DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:mime-version:content-type:content-transfer-encoding;
	b=FNK84VPNis34CDzZGGygJpuz4CN+p44Yzxin/u74aoXFA+Wx45wluvKmcEKPdzF8IuRT//F1TI973rwvTNCBG+kd3UYVlwQ8Vdf12HHscXWfG9hIFJG68uJMont+T9ECsZAxf/uEmGG58nrDMRUZvUTAKKegnvsocSWd74lb8aU=
Message-ID: <758d5e7f05011302265be7e12a@mail.gmail.com>
Date: Thu, 13 Jan 2005 11:26:04 +0100
From: Dawid Kuroczko <qnex42@gmail.com>
Reply-To: Dawid Kuroczko <qnex42@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Columns into rows.
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

I'm working with a quite flat table schema (think: mySQL ;)),
and I want to divide it into two tables.

Lets start with how it looks now:
CREATE TABLE old_table (
  id serial PRIMARY KEY,
  body text,
  path_a varchar(1024),
  gendate_a date,
  path_bb varchar(1024),
  gendate_bb date,
  path_ccc varchar(1024),
  gendate_ccc date
);

I want to have:
CREATE TABLE new_table (
  id serial PRIMARY KEY,
  body text
);
CREATE TABLE new_table_paths (
  id integer NOT NULL REFERENCES (new_table),
  pathtype NOT NULL varchar(10),
  path varchar(1024),
  gendate date,
  PRIMARY KEY(id,pathtype)
);

...what I'm looking for is how to, most efficiently
write a join between new_table and new_table_paths
so it looks like old_table (for compatibility (for other
people)).

I'm thinking about a trigger on schema-table
(with pathtypes), which would automagically update
view whenever schema-table is updated (new pathtype
(like 'a', 'bb', 'ccc')) is added/removed); but this is
implementation.

Now, for the join.

I can write:

SELECT * FROM (SELECT id, path AS path_a FROM new_table_paths WHERE id
= <<NUM>> AND pathtype = 'a') AS a NATURAL FULL OUTER JOIN (SELECT id,
path AS path_bb FROM new_table_paths WHERE id = <<NUM>> AND pathtype =
'bb') AS bb; [ and so on... ]

And its fast; however if I move WHERE id = <<NUM>> outside selects
(for views), it first "materializes" old layout, and then selects
id...  total waste.

SELECT * FROM (SELECT id, path AS path_a FROM new_table_paths WHERE
pathtype = 'a') AS a NATURAL FULL OUTER JOIN (SELECT id, path AS
path_bb FROM new_table_paths WHERE pathtype = 'bb') AS bb WHERE id =
<<NUM>>;

I wonder if you know other way to write this join so it has good performace?
Once again, I need a SELECT since I want a VIEW. :-)

   Regards,
      Dawid