Why is my view ddl being altered by postgres?
От | Brent Friedman |
---|---|
Тема | Why is my view ddl being altered by postgres? |
Дата | |
Msg-id | 46FD50C8.3090602@scanonline.com обсуждение исходный текст |
Ответы |
Re: Why is my view ddl being altered by postgres?
Re: Why is my view ddl being altered by postgres? Re: Why is my view ddl being altered by postgres? |
Список | pgsql-novice |
I am new to this list, and pretty new to postgres. I have used Oracle, DB2, MS Sql Server, etc., for several years, but I still run into things unique to postgres that stump me. I am running Postgres 8.0.13 on a 2.6.5-xx smp Linux kernel. I tried rewriting an existing view based on a multi-way join into several smaller views, to keep the RAM footprint of a reporting query as small as possible. One of these views (in a series) is being corrupted/changed by postgres. 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: 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 JOIN table1 ON vw2.other_table_id = table1.other_table_id ORDER BY vw2.station_transactionid, vw2.accepted, vw2.rejected, vw2.bulk, vw2.individual, CASE WHEN table1.value::text = 'V001'::text THEN 1 ELSE 0 END, CASE WHEN table1.value::text = 'V002'::text THEN 1 ELSE 0 END, CASE WHEN table1.value::text = 'V003'::text THEN 1 ELSE 0 END; If there is something postgres-centric that I am missing, please let me know.
В списке pgsql-novice по дате отправления: