Lists: | pgsql-general |
---|
From: | raf <raf(at)raf(dot)org> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | select t.name from tbl t (where "name" is not a column name) |
Date: | 2010-02-24 01:07:50 |
Message-ID: | 20100224010750.GA18679@raf.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Joe Conway <mail(at)joeconway(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: select t.name from tbl t (where "name" is not a column name) |
Date: | 2010-02-24 02:18:59 |
Message-ID: | 4B848C93.1080900@joeconway.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | "Igor Neyman" <ineyman(at)perceptron(dot)com> |
---|---|
To: | "Joe Conway" <mail(at)joeconway(dot)com>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: select t.name from tbl t (where "name" is not a column name) |
Date: | 2010-02-24 15:16:21 |
Message-ID: | F4C27E77F7A33E4CA98C19A9DC6722A20598C154@EXCHANGE.corp.perceptron.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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(at)joeconway(dot)com]
> Sent: Tuesday, February 23, 2010 9:19 PM
> To: pgsql-general(at)postgresql(dot)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
>
>
From: | Joe Conway <mail(at)joeconway(dot)com> |
---|---|
To: | Igor Neyman <ineyman(at)perceptron(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: select t.name from tbl t (where "name" is not a column name) |
Date: | 2010-02-24 17:49:17 |
Message-ID: | 4B85669D.5000907@joeconway.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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