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,
CONSTRAINT a PRIMARY KEY (y1)
CREATE TABLE z
z1 int4 NOT NULL,
CONSTRAINT zz PRIMARY KEY (z1)
CREATE TABLE x
x1 int4 NOT NULL,
xy1 int4 NOT NULL,
xz1 int4 NOT NULL,
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;
On Jan 14, 2008 11:49 PM, Patric <lists(at)p-dw(dot)com> wrote:
> 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,
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
In response to
pgsql-performance by date
|Next:||From: Jakub Ouhrabka||Date: 2008-01-15 12:09:18|
|Subject: Re: Linux/PostgreSQL scalability issue - problem with 8
|Previous:||From: Shane Ambler||Date: 2008-01-14 19:06:42|
|Subject: Re: Seq scans on indexed columns.|