This is why I needed to pass it by the backend gurus :)
Thanks for pointing out these additional issues, Jan.
> > This is on the TODO list.
> > I actually have a solution that seems to work fine, but I wanted to run it
> > 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 ON c * FROM t2;
> Populate them with:
> 1 'one'
> 1 'ena'
> 2 'two'
> 2 'thio'
> 3 'three'
> 3 'tria'
> 4 'four'
> 4 'tessera'
> 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 from 1 to 33 in english
> and greek) and column d must show either the roman or german
> 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 clauses can only
> appear once per query, so there is no room where the rewrite
> system can place multiple different ones. Implementing this
> requires first dramatic changes to the querytree layout and I
> think it needs subselecting RTE's too.
> Sorry - Jan
> # It's easier to get forgiveness for being wrong than for being right. #
> # Let's break this rule - forgive me. #
> #======================================== jwieck(at)debis(dot)com (Jan Wieck) #
pgsql-hackers by date
|Next:||From: Clark Evans||Date: 1999-04-26 19:00:23|
|Subject: Re: [INTERFACES] CASE tools? (slightly off-topic)|
|Previous:||From: Todd Graham Lewis||Date: 1999-04-26 18:45:51|
|Subject: Re: [HACKERS] Re: CORBA again. (was: light dawns: serious bug in
FE/BE protocol handling)|