Re: [HACKERS] create view as select distinct (fwd)

Hehe,

This is why I needed to pass it by the backend gurus :)

Thanks for pointing out these additional issues, Jan.

-Ryan

> > 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 ON c * 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 from 1 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 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) #
>
>