This implies that the "group by" clause is not supported in views. I have
created views that use the group by clause and they appear to work. I have
not verified the content of the records. I would like to know more about
what Jan means when he says that "group by" is not supported in views? Does
it mean that the content of the results could be unexpected or are they
conditions where they may work and other conditions where they don't work?
More info would be greatly appreciated.
Thanks, Michael
-----Original Message-----From: jwieck@debis.com [SMTP:jwieck@debis.com]Sent: Monday, April 26, 1999 9:35 AMTo:
rbrad@hpb50023.boi.hp.comCc: pgsql-hackers@postgreSQL.orgSubject: Re: [HACKERS] create view as select distinct
(fwd)
>> This is on the TODO list.>> I actually have a solution that seems to work fine, but I wanted
to run it past> the backend guru's after we have finished the 6.5 beta.>> Sorry I din't get it finished before the beta
started.>>-Ryan
I wonder how it does!
Have the following:
CREATE TABLE t1 (a int4, b text); CREATE TABLE t2 (c int4, d text); CREATE VIEW v2 AS SELECT DISTINCT
ONc * FROM t2;
Populate them with:
t1: 1 'one' 1 'ena' 2 'two' 2 'thio' 3 'three' 3 'tria' 4
'four' 4 'tessera'
t2: 1 'I' 1 'eins' 2 'II' 2 'zwei' 3 'III' 3 'drei'
Now you do
SELECT t1.a, t1.b, v2.d FROM t1, v2 WHERE t1.a = v2.c;
Does that work and produce the correct results? Note that there are more than one correct results. The
DISTINCT SELECT from t2 already has. But in any case, the above SELECT should present 6 rows (all the rows of t1
from1 to 33 in english and greek) and column d must show either the roman or german number.
To make it more complicated, add table t3 and populate it with more languages. Then setup
CREATE VIEW v3 AS SELECT DISTINCT ON e * FROM t3;
and expand the above SELECT to a join over t1, v2, v3.
Finally, think about a view that is a DISTINCT SELECT over multiple tables. Now you build another view as
SELECT from the first plus some other table and make the new view DISTINCT again.
The same kind of problem causes that views currently cannot have ORDER BY or GROUP BY clauses. All these
clausescan only appear once per query, so there is no room where the rewrite system can place multiple
differentones. Implementing this requires first dramatic changes to the querytree layout and I think it needs
subselectingRTE's too.
Sorry - Jan
--
#======================================================================## It's easier to get forgiveness for being
wrongthan for being
right. ## Let's break this rule - forgive me.
##======================================== jwieck@debis.com (Jan
Wieck) #
