Skip site navigation (1) Skip section navigation (2)

Re: Saving result set of SELECT to table column

From: "Robins Tharakan" <tharakan(at)gmail(dot)com>
To: Patric <lists(at)p-dw(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Saving result set of SELECT to table column
Date: 2008-01-15 03:30:27
Message-ID: 36af4bed0801141930r4bcaf94cqed26557899fc578b@mail.gmail.com (view raw or flat)
Thread:
Lists: 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(at)p-dw(dot)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
>

In response to

pgsql-performance by date

Next:From: Jakub OuhrabkaDate: 2008-01-15 12:09:18
Subject: Re: Linux/PostgreSQL scalability issue - problem with 8 cores
Previous:From: Shane AmblerDate: 2008-01-14 19:06:42
Subject: Re: Seq scans on indexed columns.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group