Re: Why is my view ddl being altered by postgres? - Mailing list pgsql-novice

Brent Friedman <bfriedman@scanonline.com> writes:
>  I am creating the view via a web tool (phppgadmin) with this ddl:

> CREATE VIEW vw_data_3 AS
> SELECT DISTINCT vw2.other_table_id, vw2.somedata1, vw2.somedata2,
> vw2.somedata3, vw2.somedata4,
> CASE
>   WHEN table1.value::text = 'V001'::text
>      THEN 1
>      ELSE 0
>   END AS value1,
> CASE
>   WHEN table1.value::text = 'V002'::text
>      THEN 1
>      ELSE 0
>   END AS value2,
> CASE
>   WHEN table1.value::text = 'V003'::text
>      THEN 1
>      ELSE 0
>   END AS value3
> from vw_data_2 vw2
> LEFT OUTER JOIN table1
>  ON vw2.other_table_id = table1.other_table_id
> ORDER BY vw2.other_table_id;

> However, looking at the definition of this view in phppgadmin, the ORDER
> BY clause gets messed up:

It's not "messed up", it's just doing what it has to do to implement the
DISTINCT.  Namely, sort all the rows into order.  The original ORDER BY
is still satisfied.

This is an implementation detail that maybe we should make more effort
to hide, but it's not wrong.

            regards, tom lane