Thread: Bug #545: Keyword USER not detected in CREATE VIEW
Avi Kivity (avi@argo.co.il) reports a bug with a severity of 3 The lower the number the more severe it is. Short Description Keyword USER not detected in CREATE VIEW Long Description The reserved keyword USER is rejected in CREATE TABLE, as expected: avi=> create table x (user varchar); ERROR: parser: parse error at or near "user" In my ignorance, however, I tried to create a view with a field named user: avi=> create table y (x varchar); CREATE avi=> create view z as select x as user from y; CREATE Note no error. avi=> \d z View "z" Attribute | Type | Modifier -----------+-------------------+---------- user | character varying | View definition: SELECT y.x AS "user" FROM y; avi=> insert into y values ('zz'); INSERT 43771 1 avi=> select user from z; current_user -------------- avi (1 row) Wrong value returned. avi=> select "user" from z; user ------ zz (1 row) Correct value returned. My expected result: an error message in CREATE VIEW Sample Code No file was uploaded with this report
This is because user is a "reserved" word. user is a macro for the function current_user() Try this SQL: SELECT user; It is best to change the table name from user to username; However it is probably a wise idea for Postgres to emit some sort of error on the create mentioning the reserved word. At 11:23 AM 12/28/01 -0500, you wrote: >Avi Kivity (avi@argo.co.il) reports a bug with a severity of 3 >The lower the number the more severe it is. > >Short Description >Keyword USER not detected in CREATE VIEW > >Long Description >The reserved keyword USER is rejected in CREATE TABLE, as expected: > > avi=> create table x (user varchar); > ERROR: parser: parse error at or near "user" > >In my ignorance, however, I tried to create a view with a field named user: > > avi=> create table y (x varchar); > CREATE > avi=> create view z as select x as user from y; > CREATE > >Note no error. > > avi=> \d z > View "z" > Attribute | Type | Modifier > -----------+-------------------+---------- > user | character varying | > View definition: SELECT y.x AS "user" FROM y; > > avi=> insert into y values ('zz'); > INSERT 43771 1 > avi=> select user from z; > current_user > -------------- > avi > (1 row) > >Wrong value returned. > > avi=> select "user" from z; > user > ------ > zz > (1 row) > >Correct value returned. > >My expected result: an error message in CREATE VIEW > > >Sample Code > > >No file was uploaded with this report > > >---------------------------(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 > >
pgsql-bugs@postgresql.org writes: > avi=> create view z as select x as user from y; > My expected result: an error message in CREATE VIEW This is not a bug, it is intentional behavior. "AS" names are not restricted to be non-reserved words. regards, tom lane
> > avi=> create view z as select x as user from y; > > My expected result: an error message in CREATE VIEW > This is not a bug, it is intentional behavior. "AS" names are not > restricted to be non-reserved words. Sure, but his point is that the resulting view has a column which is named "user" and which is not directly accessible without using double quotes. I'm not sure that would qualify as a bug, but certainly is a gotcha... - Thomas
Thomas Lockhart <lockhart@fourpalms.org> writes: > avi=> create view z as select x as user from y; > My expected result: an error message in CREATE VIEW >> This is not a bug, it is intentional behavior. "AS" names are not >> restricted to be non-reserved words. > Sure, but his point is that the resulting view has a column which is > named "user" and which is not directly accessible without using double > quotes. Yeah, but what would you do about it? The identical situation can be created in at least three ways: create table foo ("user" varchar); create view foo as select x as "user" from y; create view foo as select x as user from y; I would certainly object to emitting a warning notice in the first two cases. There might be some merit in warning about the third, but I don't see a reasonable way to distinguish it from the others. A not-unrelated point: would you also have the system warn about create table foo ("Foo" varchar); Here again the column cannot be named without using quotes. But I'd see issuing a warning about it as unwarranted nannyism. regards, tom lane
... > Here again the column cannot be named without using quotes. But I'd > see issuing a warning about it as unwarranted nannyism. Yup. I'm not suggesting a change, but the more explicit discussion on *why* this may be acceptable is probably helpful. I think we are pretty close in 'tude on the nannyism issues; I like building bigger guns too ;) - Thomas