Postgres Pro
Downloads
Home   >   mailing lists

Thread: create view bug

create view bug

From
Jefim Matskin
Date:
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"

Using PostgerSQL 7.1 on linux redhat kernel 2.2.19 intel.

> Jefim Matskin
>
>
>

Re: create view bug

From
Tom Lane
Date:
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

Re: create view bug

From
Tom Lane
Date:
> 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

Re: create view bug

From
Tom Lane
Date:
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

Re: create view bug

From
Tom Lane
Date:
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

Re: create view bug

From
Bruce Momjian
Date:
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