Grokbase › Groups › PostgreSQL › pgsql-hackers › September 2010
FAQ
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

Search Discussions

  • Heikki Linnakangas at Sep 24, 2010 at 10:35 am ⇧

    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
  • Robert Haas at Sep 24, 2010 at 12:01 pm ⇧

    On Fri, Sep 24, 2010 at 6:35 AM, Heikki Linnakangas 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
  • André Fernandes at Sep 24, 2010 at 3:29 pm ⇧

    Date: Fri, 24 Sep 2010 08:01:35 -0400
    Subject: Re: [HACKERS] Name column
    From: [email protected]
    To: [email protected]
    CC: [email protected]; [email protected]

    On Fri, Sep 24, 2010 at 6:35 AM, Heikki Linnakangas
    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.
  • Tom Lane at Sep 24, 2010 at 3:44 pm ⇧

    =?iso-8859-1?B?QW5kcukgRmVybmFuZGVz?= <[email protected]> writes:
    On Fri, Sep 24, 2010 at 6:35 AM, Heikki Linnakangas
    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
  • Robert Haas at Sep 24, 2010 at 3:48 pm ⇧

    On Fri, Sep 24, 2010 at 11:43 AM, Tom Lane wrote:
    =?iso-8859-1?B?QW5kcukgRmVybmFuZGVz?= <[email protected]> writes:
    On Fri, Sep 24, 2010 at 6:35 AM, Heikki Linnakangas
    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
  • Tom Lane at Sep 24, 2010 at 3:55 pm ⇧

    Robert Haas writes:
    On Fri, Sep 24, 2010 at 11:43 AM, Tom Lane 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
  • Robert Haas at Sep 24, 2010 at 4:03 pm ⇧

    On Fri, Sep 24, 2010 at 11:55 AM, Tom Lane wrote:
    Robert Haas <[email protected]> writes:
    On Fri, Sep 24, 2010 at 11:43 AM, Tom Lane 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
  • Tom Lane at Sep 24, 2010 at 4:11 pm ⇧

    Robert Haas writes:
    On Fri, Sep 24, 2010 at 11:55 AM, Tom Lane 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 at Sep 24, 2010 at 4:17 pm ⇧

    2010/9/24 Tom Lane <[email protected]>:
    Robert Haas <[email protected]> writes:
    On Fri, Sep 24, 2010 at 11:43 AM, Tom Lane 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 ([email protected])
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-hackers
  • Pavel Stehule at Sep 24, 2010 at 4:01 pm ⇧
    2010/9/24 André Fernandes <[email protected]>:
    Date: Fri, 24 Sep 2010 08:01:35 -0400
    Subject: Re: [HACKERS] Name column
    From: [email protected]
    To: [email protected]
    CC: [email protected]; [email protected]

    On Fri, Sep 24, 2010 at 6:35 AM, Heikki Linnakangas
    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
    >
  • Tom Lane at Sep 24, 2010 at 4:16 pm ⇧

    Pavel Stehule 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedSep 24, '10 at 10:28a
activeSep 24, '10 at 4:17p
posts12
users6
websitepostgresql.org...
irc#postgresql

6 users in discussion

Tom Lane: 4 posts Robert Haas: 3 posts Pavel Stehule: 2 posts Heikki Linnakangas: 1 post Vlad Arkhipov: 1 post André Fernandes: 1 post

Content

People

Support

Translate

site design / logo © 2023 Grokbase