From: | Brent Friedman <bfriedman(at)scanonline(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Why is my view ddl being altered by postgres? |
Date: | 2007-09-28 19:06:48 |
Message-ID: | 46FD50C8.3090602@scanonline.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Jorge Alberto | 2007-09-28 19:40:03 | ERROR: must be superuser to COPY to or from a file |
Previous Message | Sean Davis | 2007-09-27 11:54:25 | Re: Long count(*) time |