Обсуждение: Name column
I have just come across a weird thing. It works for any table and seems to be not documented. SELECT c.name FROM (VALUES(1, 'A', true)) c; SELECT c.name FROM pg_class c; And it does not work in these cases: SELECT name FROM (VALUES(1, 'A', true)); SELECT name FROM pg_class; PostgreSQL 8.4.2 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42), 64-bit
On 24/09/10 13:02, Vlad Arkhipov wrote: > I have just come across a weird thing. It works for any table and seems > to be not documented. > > SELECT c.name FROM (VALUES(1, 'A', true)) c; > SELECT c.name FROM pg_class c; > > And it does not work in these cases: > > SELECT name FROM (VALUES(1, 'A', true)); > SELECT name FROM pg_class; For historical reasons PostgreSQL supports calling a function with a single argument like "column.function", in addition to "function(column)". There is a function "name(text)" that casts the input to the 'name' datatype, so your example casts the row to text and from text to name. It is mentioned in the documentation at http://www.postgresql.org/docs/8.4/interactive/xfunc-sql.html Section "34.4.2. SQL Functions on Composite Types". -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
On Fri, Sep 24, 2010 at 6:35 AM, Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> wrote: > For historical reasons PostgreSQL supports calling a function with a single > argument like "column.function", in addition to "function(column)". There is > a function "name(text)" that casts the input to the 'name' datatype, so your > example casts the row to text and from text to name. I'm starting to wonder if we should think about deprecating this behavior. It is awfully confusing and unintuitive. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company
<br /><br />> Date: Fri, 24 Sep 2010 08:01:35 -0400<br />> Subject: Re: [HACKERS] Name column<br />> From: robertmhaas@gmail.com<br/>> To: heikki.linnakangas@enterprisedb.com<br />> CC: arhipov@dc.baikal.ru; pgsql-hackers@postgresql.org<br/>> <br />> On Fri, Sep 24, 2010 at 6:35 AM, Heikki Linnakangas<br />> <heikki.linnakangas@enterprisedb.com>wrote:<br />> > For historical reasons PostgreSQL supports calling a functionwith a single<br />> > argument like "column.function", in addition to "function(column)". There is<br />>> a function "name(text)" that casts the input to the 'name' datatype, so your<br />> > example casts therow to text and from text to name.<br />> <br />> I'm starting to wonder if we should think about deprecating this<br/>> behavior. It is awfully confusing and unintuitive.<br />> <br /><br />I agree, it is very unintuitive. <br/>+1 for deprecating this behavior.<br />
André Fernandes <andre.de.camargo.fernandes@hotmail.com> writes:
>> On Fri, Sep 24, 2010 at 6:35 AM, Heikki Linnakangas
>> <heikki.linnakangas@enterprisedb.com> wrote:
>> I'm starting to wonder if we should think about deprecating this
>> behavior. It is awfully confusing and unintuitive.
> I agree, it is very unintuitive.
> +1 for deprecating this behavior.
-1. There's nothing wrong with the function-as-a-computed-column
feature, and it seems likely that taking it away will break applications.
What we are getting bit by is that I/O coercions to string types can be
specified this way. Maybe what we ought to do is remove just that one
capability. It'd be a bit non-orthogonal, but seems fairly unlikely to
break anything, especially since we only began to allow such things
recently (in 8.4 looks like).
regards, tom lane
On Fri, Sep 24, 2010 at 11:43 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > André Fernandes <andre.de.camargo.fernandes@hotmail.com> writes: >>> On Fri, Sep 24, 2010 at 6:35 AM, Heikki Linnakangas >>> <heikki.linnakangas@enterprisedb.com> wrote: >>> I'm starting to wonder if we should think about deprecating this >>> behavior. It is awfully confusing and unintuitive. > >> I agree, it is very unintuitive. >> +1 for deprecating this behavior. > > -1. There's nothing wrong with the function-as-a-computed-column > feature, and it seems likely that taking it away will break applications. > > What we are getting bit by is that I/O coercions to string types can be > specified this way. Maybe what we ought to do is remove just that one > capability. It'd be a bit non-orthogonal, but seems fairly unlikely to > break anything, especially since we only began to allow such things > recently (in 8.4 looks like). I think that might be an improvement, but I'm not convinced it goes far enough. What evidence do we have that anyone is relying on this behavior in applications? Every report I've heard of it involved someone being surprised that it worked that way. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company
Robert Haas <robertmhaas@gmail.com> writes:
> On Fri, Sep 24, 2010 at 11:43 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> -1. �There's nothing wrong with the function-as-a-computed-column
>> feature, and it seems likely that taking it away will break applications.
> ... What evidence do we have that anyone is relying on this
> behavior in applications? Every report I've heard of it involved
> someone being surprised that it worked that way.
So? There are lots of surprising things in SQL. And *of course* the
only complaints come from people who didn't know about it, not from
satisfied users.
The reason people don't know about this feature is that it's so poorly
documented --- there's just one mention buried deep in chapter 35 of
the manual, in a place where most people wouldn't think to look for it.
I'm not quite sure where's a better place though.
regards, tom lane
2010/9/24 André Fernandes <andre.de.camargo.fernandes@hotmail.com>: > > >> Date: Fri, 24 Sep 2010 08:01:35 -0400 >> Subject: Re: [HACKERS] Name column >> From: robertmhaas@gmail.com >> To: heikki.linnakangas@enterprisedb.com >> CC: arhipov@dc.baikal.ru; pgsql-hackers@postgresql.org >> >> On Fri, Sep 24, 2010 at 6:35 AM, Heikki Linnakangas >> <heikki.linnakangas@enterprisedb.com> wrote: >> > For historical reasons PostgreSQL supports calling a function with a >> > single >> > argument like "column.function", in addition to "function(column)". >> > There is >> > a function "name(text)" that casts the input to the 'name' datatype, so >> > your >> > example casts the row to text and from text to name. >> >> I'm starting to wonder if we should think about deprecating this >> behavior. It is awfully confusing and unintuitive. >> > > I agree, it is very unintuitive. > +1 for deprecating this behavior. +1 I dislike this feature too. It is breaking other ANSI SQL feature - constructors, because it has same syntax tablename(field1, field2, ....). Sure, usually we can do ROW(a,b,c)::type - but little bit nicer and with standard is type(a,b,c). Regards Pavel Stehule >
On Fri, Sep 24, 2010 at 11:55 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.com> writes: >> On Fri, Sep 24, 2010 at 11:43 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> -1. There's nothing wrong with the function-as-a-computed-column >>> feature, and it seems likely that taking it away will break applications. > >> ... What evidence do we have that anyone is relying on this >> behavior in applications? Every report I've heard of it involved >> someone being surprised that it worked that way. > > So? There are lots of surprising things in SQL. And *of course* the > only complaints come from people who didn't know about it, not from > satisfied users. I guess that's true, but is this behavior specified in or required by any SQL standard? Are there other database products that also support this syntax? Or is this just our own invention? > The reason people don't know about this feature is that it's so poorly > documented --- there's just one mention buried deep in chapter 35 of > the manual, in a place where most people wouldn't think to look for it. > I'm not quite sure where's a better place though. I think it's because it's counterintuitive. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company
Robert Haas <robertmhaas@gmail.com> writes:
> On Fri, Sep 24, 2010 at 11:55 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> So? �There are lots of surprising things in SQL. �And *of course* the
>> only complaints come from people who didn't know about it, not from
>> satisfied users.
> I guess that's true, but is this behavior specified in or required by
> any SQL standard? Are there other database products that also support
> this syntax? Or is this just our own invention?
It's a holdover from PostQUEL, I think, but it's still useful. I
observe that SQL:2008 has added a significantly-uglier-than-this feature
for computed columns, so there's certainly use cases out there.
> I think it's because it's counterintuitive.
From an object-oriented-programming standpoint it seems entirely
intuitive. Many OOP languages minimize the notational difference
between members and methods of a class.
regards, tom lane
Pavel Stehule <pavel.stehule@gmail.com> writes:
> I dislike this feature too. It is breaking other ANSI SQL feature -
> constructors, because it has same syntax tablename(field1, field2,
> ....).
Uh, that's nonsense. What we're talking about is tablename.functionname.
regards, tom lane
2010/9/24 Tom Lane <tgl@sss.pgh.pa.us>: > Robert Haas <robertmhaas@gmail.com> writes: >> On Fri, Sep 24, 2010 at 11:43 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> -1. There's nothing wrong with the function-as-a-computed-column >>> feature, and it seems likely that taking it away will break applications. > >> ... What evidence do we have that anyone is relying on this >> behavior in applications? Every report I've heard of it involved >> someone being surprised that it worked that way. > > So? There are lots of surprising things in SQL. And *of course* the > only complaints come from people who didn't know about it, not from > satisfied users. > > The reason people don't know about this feature is that it's so poorly > documented --- there's just one mention buried deep in chapter 35 of > the manual, in a place where most people wouldn't think to look for it. > I'm not quite sure where's a better place though. I hope so nobody use it. It is absolutely out of standard. It is like own syntax of mysql for some SQL statements like own INSERT. Some people talked so these specialities are useful too. Regards Pavel Stehule > > regards, tom lane > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >