Thread: Strange CREATE VIEW behavior??

Basil A. Evseenko (evseenko@msiu.ru) reports a bug with a severity of 3
The lower the number the more severe it is.

Short Description
Strange CREATE VIEW behavior??

Long Description
createdb test
psql test

test=# create table zz(ss int, ff varchar);
test=# create view ff2 as select ff as user, ss as num from zz;
test=# create view ff3 as select ff as user1, ss as num from zz;
test=# \d ff2
               View "ff2"
 Attribute |       Type        | Modifier
-----------+-------------------+----------
 user      | character varying |
 num       | integer           |
View definition: SELECT zz.ff AS "user", zz.ss AS num FROM zz;

test=# \d ff3
                View "ff3"
 Attribute |       Type        | Modifier
-----------+-------------------+----------
 user1     | character varying |
 num       | integer           |
View definition: SELECT zz.ff AS user1, zz.ss AS num FROM zz;

Why in the first case column has name "user" (with '"') ?

PostgreSQL 7.1.2 builded gcc 2.95.3 with options:
./configure  --prefix=/usr --sysconfdir=/etc --enable-locale --enable-multibyte=KOI8 --enable-unicode-conversion
--with-tcl --with-perl --with-python --with-openssl --with-CXX --with-gnu-ld --enable-syslog

Linux-2.2.19 on i686, glibc-2.1.3

Sample Code


No file was uploaded with this report

On Thu, 14 Jun 2001 pgsql-bugs@postgresql.org wrote:

> Basil A. Evseenko (evseenko@msiu.ru) reports a bug with a severity of 3
> The lower the number the more severe it is.
>
> Short Description
> Strange CREATE VIEW behavior??
>
> Long Description
> createdb test
> psql test
>
> test=# create table zz(ss int, ff varchar);
> test=# create view ff2 as select ff as user, ss as num from zz;
> test=# create view ff3 as select ff as user1, ss as num from zz;
> test=# \d ff2
>                View "ff2"
>  Attribute |       Type        | Modifier
> -----------+-------------------+----------
>  user      | character varying |
>  num       | integer           |
> View definition: SELECT zz.ff AS "user", zz.ss AS num FROM zz;
>
> test=# \d ff3
>                 View "ff3"
>  Attribute |       Type        | Modifier
> -----------+-------------------+----------
>  user1     | character varying |
>  num       | integer           |
> View definition: SELECT zz.ff AS user1, zz.ss AS num FROM zz;
>
> Why in the first case column has name "user" (with '"') ?

Probably because user is sometimes reserved.  For example:
create table foo(user int); -- fails
create table foo("user" int); -- succeeds

pgsql-bugs@postgresql.org writes:
> View definition: SELECT zz.ff AS "user", zz.ss AS num FROM zz;

> View definition: SELECT zz.ff AS user1, zz.ss AS num FROM zz;

> Why in the first case column has name "user" (with '"') ?

USER is a keyword.  It happens not to be reserved, so you can use it for
an AS name without quoting it, but the view decompiler doesn't want to
take any chances so it quotes it anyway.

It would be correct and 100% safe for Postgres to display these rules
with all identifiers quoted:

View definition: SELECT "zz"."ff" AS "user1", "zz"."ss" AS "num" FROM "zz";

but since that's pretty unreadable, we try to suppress the quotes where
they're not essential.  The decompiler just errs on the side of safety
when it sees that the identifier matches a keyword: rather than trying
to figure out if the keyword is reserved in this particular context,
it just adds the quotes always.

            regards, tom lane