pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSaving result set of SELECT to table column
3+ messages / 3 participants
[nested] [flat]
* Saving result set of SELECT to table column
@ 2008-01-14 18:19 Patric <lists@p-dw.com>
2008-01-14 18:38 ` Re: Saving result set of SELECT to table column Ansgar -59cobalt- Wiechers <lists@planetcobalt.net>
2008-01-15 03:30 ` Re: Saving result set of SELECT to table column Robins Tharakan <tharakan@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Patric @ 2008-01-14 18:19 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Saving result set of SELECT to table column
2008-01-14 18:19 Saving result set of SELECT to table column Patric <lists@p-dw.com>
@ 2008-01-14 18:38 ` Ansgar -59cobalt- Wiechers <lists@planetcobalt.net>
1 sibling, 0 replies; 3+ messages in thread
From: Ansgar -59cobalt- Wiechers @ 2008-01-14 18:38 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Saving result set of SELECT to table column
2008-01-14 18:19 Saving result set of SELECT to table column Patric <lists@p-dw.com>
@ 2008-01-15 03:30 ` Robins Tharakan <tharakan@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Robins Tharakan @ 2008-01-15 03:30 UTC (permalink / raw)
To: Patric <lists@p-dw.com>; +Cc: pgsql-performance
Hi Patric,
This doesn't seem to be a question pertaining to the PERFORM queue.
If I understand you correctly, this should solve your problems, without the
need for any RULES / TRIGGERS.
CREATE TABLE y
(
y1 int4 NOT NULL,
y2 varchar,
CONSTRAINT a PRIMARY KEY (y1)
)
CREATE TABLE z
(
z1 int4 NOT NULL,
z2 varchar,
CONSTRAINT zz PRIMARY KEY (z1)
)
CREATE TABLE x
(
x1 int4 NOT NULL,
xy1 int4 NOT NULL,
xz1 int4 NOT NULL,
xy2 varchar,
xz2 varchar,
CONSTRAINT xa PRIMARY KEY (x1),
CONSTRAINT xy1 FOREIGN KEY (xy1)
REFERENCES y (y1) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT xz1 FOREIGN KEY (xz1)
REFERENCES z (z1) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
INSERT INTO x (x1, xy1, xz1, xy2, xz2)
SELECT 1, y1, z1, y2, z2
FROM y, z
WHERE y1 = 1
AND z1 = 1;
*Robins*
On Jan 14, 2008 11:49 PM, Patric <lists@p-dw.com> wrote:
> 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
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2008-01-15 03:30 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-01-14 18:19 Saving result set of SELECT to table column Patric <lists@p-dw.com>
2008-01-14 18:38 ` Ansgar -59cobalt- Wiechers <lists@planetcobalt.net>
2008-01-15 03:30 ` Robins Tharakan <tharakan@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