Thread: Columns into rows.
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_bbdate, 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
On Thu, 13 Jan 2005 11:26:04 +0100, Dawid Kuroczko <qnex42@gmail.com> wrote: > 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. :-) Hmm, something like a multiple column returning aggregate... As far as I know it is not possible to create aggregate which could return more than one column? I'm thinking about something like this: SELECT id,r2c_aggregate(pathtype, path) FROM new_table_paths GROUP BY id; ...where r2c_aggregate(..) would be a plpgsql function doing the pathtype+path --> path_a = path, path_bb = path2, path_ccc = path3 assembly. And it would be quite efficient I guess! :) Hmm, r2c_aggregate could return array of arrays of pathtype/path pairs, which could be processed by other function which would convert them into rows, but this is UGLY(TM). :) Regards, Dawid
On Thu, 13 Jan 2005 11:45:52 +0100, Dawid Kuroczko <qnex42@gmail.com> wrote: > > 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>>; Part of the problem solved: CREATE AGGREGATE array_aggregate (basetype = anyelement, sfunc = array_append, stype = anyarray, initcond = '{}'); SELECT id,array_aggregate(pathtype||'='||path) from new_table_paths group by id; I.e. have a nice array of type=value pairs. I have however failed to write a function which would take text[] and return columns. I tried using RETURN record type, but so far it does not return anything useful (just a parenthesis enclosed list of column values). Any hints?