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 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@debis.com (Jan Wieck) #