pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Herouth Maoz <herouth@oumail.openu.ac.il>
To: John M. Flinchbaugh <glynis@butterfly.hjsoft.com>
To: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] ERROR:  DefineQueryRewrite: rule plan string too big.
Date: Sun, 25 Jul 1999 15:54:28 +0300
Message-ID: <l03130303b3c0b849086d@[147.233.159.109]> (raw)
In-Reply-To: <Pine.LNX.4.10.9907231636450.26275-100000@butterfly.hjsoft.com>

At 23:45 +0300 on 23/07/1999, John M. Flinchbaugh wrote:


> create view allinfo as select b.name as building_name,b.description as
> building_description,b.location as building_location,b.address as
> building_address,f.location as floor,r.name as room_name,r.location as
> room_location,r.dept as room_department,rt.name as room_type,h.mac as
> mac,h.name as hardware_name,h.ip as ip,h.mutag as mutag,rack.location as
> rack,rack.description as rack_description,h.pos as pos,h.serial as
> serial,h.inuse as inuse,m.name as model_name,m.description as
> model_description,m.model as model,c.name as model_class,manf.name as
> manufacturer from building as b,floor as f,room as r,roomtype as
> rt,hardware as h,dept as d,model as m,class as c,manufacturer as manf,rack
> where b.number=f.building and f.number=r.floor and r.number=rack.room and
> rack.number=h.rack and m.class=c.number and m.manufacturer=manf.number and
> r.dept=d.number;

First, I do believe that in the FROM clause, you don't use "as" to create
table aliases. This is done only in the target list. For table aliases you
just put the new name:

FROM building b, floor f, room r, roomtype rt, hardware h, dept d etc.

Now, here is a (braindead) workaround which doesn't require you to plan a
lot, and may help you reduce this plan into two views.

View number 1 - this same view, only with short field names:

CREATE VIEW internal1 AS
SELECT b.name as f1, b.description as f2, ....
FROM ....
WHERE ....;

View number 2 - straight select from internal1, only renaming the fields to
their longer names:

CREATE VIEW allinfo AS
SELECT f1 as building_name, f2 as building_description, f3 as ...
FROM internal1;

This may get you just under the threshold and save you a lot of thinking.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma





view thread (7+ messages)  latest in thread

Message-ID: <l03130303b3c0b849086d@[147.233.159.109]>
Permalink:  ../l03130303b3c0b849086d@%5B147.233.159.109%5D/
Also on:    postgresql.org/message-id/l03130303b3c0b849086d@[147.233.159.109]

 · 

reply

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-sql@postgresql.org
  Cc: herouth@oumail.openu.ac.il, glynis@butterfly.hjsoft.com
  Subject: Re: [SQL] ERROR:  DefineQueryRewrite: rule plan string too big.
  In-Reply-To: <l03130303b3c0b849086d@[147.233.159.109]>

* 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