pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedColumns into rows.
3+ messages / 1 participants
[nested] [flat]
* Columns into rows.
@ 2005-01-13 10:26 Dawid Kuroczko <qnex42@gmail.com>
2005-01-13 10:45 ` Re: Columns into rows. Dawid Kuroczko <qnex42@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Dawid Kuroczko @ 2005-01-13 10:26 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Columns into rows.
2005-01-13 10:26 Columns into rows. Dawid Kuroczko <qnex42@gmail.com>
@ 2005-01-13 10:45 ` Dawid Kuroczko <qnex42@gmail.com>
2005-01-13 11:31 ` Re: Columns into rows. Dawid Kuroczko <qnex42@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Dawid Kuroczko @ 2005-01-13 10:45 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Columns into rows.
2005-01-13 10:26 Columns into rows. Dawid Kuroczko <qnex42@gmail.com>
2005-01-13 10:45 ` Re: Columns into rows. Dawid Kuroczko <qnex42@gmail.com>
@ 2005-01-13 11:31 ` Dawid Kuroczko <qnex42@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Dawid Kuroczko @ 2005-01-13 11:31 UTC (permalink / raw)
To: pgsql-sql
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?
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-01-13 11:31 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-01-13 10:26 Columns into rows. Dawid Kuroczko <qnex42@gmail.com>
2005-01-13 10:45 ` Dawid Kuroczko <qnex42@gmail.com>
2005-01-13 11:31 ` Dawid Kuroczko <qnex42@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox