From: Patric <lists@p-dw.com>
To: pgsql-performance@postgresql.org
Subject: Saving result set of SELECT to table column
Date: Mon, 14 Jan 2008 19:19:36 +0100
Message-ID: <478BA7B8.2090907@p-dw.com> (raw)
Hi,
There will be some flames i suppose.
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?
Thanks in advance,
Patric
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@p-dw.com
Subject: Re: Saving result set of SELECT to table column
In-Reply-To: <478BA7B8.2090907@p-dw.com>
* 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