Why is my view ddl being altered by postgres?

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.

Responses

Browse pgsql-novice by date

  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