create view bug

Jefim Matskin <mjefim@sphera.com> writes:

The follwoing statement does not work:
create view testview (primary) as select 'true';
The error I get is:
ERROR: parser: parse error at or near "primary"

This is not a bug --- the syntax of CREATE VIEW has no provision for a
column name list after the view name. You can control the view column
names like this:

play=> create view testview as select 'true'::bool as primary;
CREATE 4581172 1
play=> \d testview
View "testview"
Attribute | Type | Modifier
-----------+---------+----------
primary | boolean |
View definition: SELECT 't'::bool AS "primary";

play=>

Note also the cast to ensure the column has a well-defined data type...

regards, tom lane

My problem is that the ErWin generates the following syntax
create view [view name] ( [col1] , [col2] ) as select [s1], [s2] from
[table name];
and as far as I know this is SQL92.

(checks spec...) You're right, SQL92 does require this syntax. Okay,
I'll put it on the to-do list.

regards, tom lane

Jefim Matskin <mjefim@sphera.com> wrote:

My problem is that the ErWin generates the following syntax
create view [view name] ( [col1] , [col2] ) as select [s1], [s2] from
[table name];
and as far as I know this is SQL92.

On looking more closely, we *do* support this syntax, and have since
release 7.0. I was misled by the fact that the on-line documentation
failed to mention it. (Docs fixed now.)

One of the column names I need is
'primary' - and the creation of the view fails.

The problem here is that PRIMARY is a reserved word in Postgres.
I realize that SQL92 describes it as a non-reserved keyword, but
trying to make it so creates ambiguities in our grammar. You'll
need to pick a different column name. Sorry.

regards, tom lane

This feature will appear in 7.2:

test=> create view aaa (x) as select relname from pg_class;
CREATE
test=> select * from aaa;
x
---------------------------------
pg_type
pg_attribute

My problem is that the ErWin generates the following syntax
create view [view name] ( [col1] , [col2] ) as select [s1], [s2] from
[table name];
and as far as I know this is SQL92.

(checks spec...) You're right, SQL92 does require this syntax. Okay,
I'll put it on the to-do list.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman@candle.pha.pa.us> writes:

This feature will appear in 7.2:
test=> create view aaa (x) as select relname from pg_class;

It's already there in 7.1.

IIRC, the real problem was that Jefim was trying to use a reserved word
as a column name.

regards, tom lane