Обсуждение: select t.name from tbl t (where "name" is not a column name)
hi, i've just noticed the following behaviour and was wondering if there's any documentation to explain what it's for. create table tbl(id serial primary key, a text, b text, c text); insert into tbl(a, b, c) values ('abc', 'def', 'ghi'); insert into tbl(a, b, c) values ('jkl', 'mno', 'pqr'); insert into tbl(a, b, c) values ('stu', 'vwx', 'yza'); select t.name from tbl t; drop table tbl; results in: name ----------------- (1,abc,def,ghi) (2,jkl,mno,pqr) (3,stu,vwx,yza) you can select t.name from tbl t even though "name" isn't the name of a column in the table and you get this tuple-like version of the each row. it doesn't work without the "t" alias and of course it doesn't work if name really is the name of a column in the table. so, what's this behaviour for, and, is there an equivalent way to do it when "name" is the name of a column? i doubt that i need this behaviour for anything. i'm just curious and didn't see any mention of this in the documentation for the select statement. cheers, raf
On 02/23/2010 05:07 PM, raf wrote: > i've just noticed the following behaviour and was wondering > if there's any documentation to explain what it's for. > > create table tbl(id serial primary key, a text, b text, c text); > insert into tbl(a, b, c) values ('abc', 'def', 'ghi'); > insert into tbl(a, b, c) values ('jkl', 'mno', 'pqr'); > insert into tbl(a, b, c) values ('stu', 'vwx', 'yza'); > select t.name from tbl t; I forget exactly where this is documented (and could not find it with a quick look), but calling t.name is the same as name(t) if a column reference is not found, and name is a function, which it is. So t.name is essentially casting the whole row as a name datatype and outputting the result. Try it with text: test=# \d foo Table "public.foo" Column | Type | Modifiers --------+---------+----------- f | integer | test=# select foo.text from foo; text ------ (-1) (1 row) test=# drop TABLE foo; DROP TABLE test=# create table foo(f int, text text); CREATE TABLE test=# insert into foo values(-1,'abc'); INSERT 0 1 test=# select foo.text from foo; text ------ abc (1 row) test=# select foo.name from foo; name ---------- (-1,abc) (1 row) HTH, Joe
Вложения
Joe, What PG version are running? 8.2 here complains when running your example: ERROR: column foo.name does not exist LINE 6: select foo.name from foo; ^ ********** Error ********** ERROR: column foo.name does not exist SQL state: 42703 Igor Neyman > -----Original Message----- > From: Joe Conway [mailto:mail@joeconway.com] > Sent: Tuesday, February 23, 2010 9:19 PM > To: pgsql-general@postgresql.org > Subject: Re: select t.name from tbl t (where "name" is not a > column name) > > On 02/23/2010 05:07 PM, raf wrote: > > i've just noticed the following behaviour and was wondering > if there's > > any documentation to explain what it's for. > > > > create table tbl(id serial primary key, a text, b text, c text); > > insert into tbl(a, b, c) values ('abc', 'def', 'ghi'); > > insert into tbl(a, b, c) values ('jkl', 'mno', 'pqr'); > > insert into tbl(a, b, c) values ('stu', 'vwx', 'yza'); > > select t.name from tbl t; > > I forget exactly where this is documented (and could not find > it with a quick look), but calling t.name is the same as > name(t) if a column reference is not found, and name is a > function, which it is. > > So t.name is essentially casting the whole row as a name > datatype and outputting the result. Try it with text: > > test=# \d foo > Table "public.foo" > Column | Type | Modifiers > --------+---------+----------- > f | integer | > > test=# select foo.text from foo; > text > ------ > (-1) > (1 row) > > test=# drop TABLE foo; > DROP TABLE > > test=# create table foo(f int, text text); CREATE TABLE > > test=# insert into foo values(-1,'abc'); INSERT 0 1 > > test=# select foo.text from foo; > text > ------ > abc > (1 row) > > test=# select foo.name from foo; > name > ---------- > (-1,abc) > (1 row) > > HTH, > > Joe > >
On 02/24/2010 07:16 AM, Igor Neyman wrote: > Joe, > > What PG version are running? > > 8.2 here complains when running your example: > > ERROR: column foo.name does not exist > LINE 6: select foo.name from foo; > ^ > > ********** Error ********** > > ERROR: column foo.name does not exist > SQL state: 42703 Prior to 8.3 you aren't able to cast a rowtype as text or name datatype, so no matching function is found. ------------- in 8.3.x ------------- contrib_regression=# select text(foo) from foo; text ------ (-1) (1 row) ------------- in 8.2.x ------------- contrib_regression=# select text(foo) from foo; ERROR: function text(foo) does not exist LINE 1: select text(foo) from foo; ^ HINT: No function matches the given name and argument types. You may need to add explicit type casts. Joe