From: Ansgar -59cobalt- Wiechers <lists@planetcobalt.net>
To: pgsql-performance@postgresql.org
Subject: Re: Saving result set of SELECT to table column
Date: Mon, 14 Jan 2008 19:38:38 +0100
Message-ID: <20080114183838.GC9522@mail.planetcobalt.net> (raw)
In-Reply-To: <478BA7B8.2090907@p-dw.com>
References: <478BA7B8.2090907@p-dw.com>
On 2008-01-14 Patric wrote:
> Well I've a normalized database..
> For instance:
>
> create table Y ( pk, data... );
> create table Z ( pk , data... );
>
> create table X ( char, references Y, references Z);
>
> SELECT * from X;
>
> Now I want to make a listing of the result set from X.
> If there are references to Z or Y (not null refs), I want to display
> that data too.
>
> Normally I would SELECT, to get that data, not in my case.
> Nearly all queries will be SELECTs, no UPDATEs or INSERTs, so need to
> optimize that case.
>
> The dirty little denormalization would look like this:
>
> create table X ( char, ref. to Y, ref. to Z, StoreY Y , StoreZ Z);
>
> On insert or update of Z or Y, I would update these two (StoreY,
> StoreZ) columns by RULE or TRIGGER..
> I know this is not nice etc.. Codd would sue for this, but in my case
> performance over beauty is ok.
> I'm looking for something like UPDATE X set StoreY=(SELECT * FROM Y
> WHERE pk=4) WHERE foreignID2Y = 4;
>
> Is there a away to accomplish this straightforward in a single
> statement without doing loops and stuff in a serverside procedure?
Looks to me like you want to (LEFT|RIGHT) OUTER JOIN the tables.
Regards
Ansgar Wiechers
--
"The Mac OS X kernel should never panic because, when it does, it
seriously inconveniences the user."
--http://developer.apple.com/technotes/tn2004/tn2118.html
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lists@planetcobalt.net
Subject: Re: Saving result set of SELECT to table column
In-Reply-To: <20080114183838.GC9522@mail.planetcobalt.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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