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.
