Re: Re: Strange error message when reference non-existent column foo."count"

On Wed, Dec 17, 2014 at 3:11 PM, David G Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
> Patrick Krecker wrote
> > I encountered this today and it was quite surprising:
> >
> > select version();
> > version
> >
> >
> ------------------------------------------------------------------------------------------------------
> > PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu
> > 4.8.2-19ubuntu1) 4.8.2, 64-bit
> >
> > create table foo as (select generate_series(1,3));
> >
> > As expected, the following fails:
> >
> > select count from foo;
> > ERROR: column "count" does not exist
> > LINE 1: select count from foo;
> > ^
> > But if I change the syntax to something I thought was equivalent:
> >
> > select foo."count" from foo;
> > count
> > -------
> > 3
> > (1 row)
> >
> > It works! This was quite surprising to me. Is this expected behavior,
> that
> > you can call an aggregate function without any parentheses (I can't find
> > any other syntax that works for count() sans parentheses, and this
> > behavior
> > doesn't occur for any other aggregate)?
>
> That fact that this is an aggregate function is beside the point - the
> syntax works for any function.
>
> The following two expressions are equivalent:
>
> count(foo) = foo.count
>
> I do not immediately recall where this is documented but it is. It should
> probably be documented or cross-referenced at:
>
>
> http://www.postgresql.org/docs/9.3/static/sql-syntax-calling-funcs.html#SQL-SYNTAX-CALLING-FUNCS-NAMED
>
> but alas that is not so.
>
> The basic idea is to hide the function invocation and allow for
> syntactically similar derived columns to be described.
>
> (goes looking)
>
> 4.2.6 - the note therein:
>
> http://www.postgresql.org/docs/9.3/static/sql-expressions.html#FIELD-SELECTION
> pointing to 35.4.3
>
> http://www.postgresql.org/docs/9.3/static/xfunc-sql.html#XFUNC-SQL-COMPOSITE-FUNCTIONS
>
> This relies on the rule that every table automatically has an implicit type
> created and so a "composite function" can act on that type. The "foo."
> reference in your example is technically referring to the type "foo" and
> not
> the table "foo".
>
> David J.
>
>
>
>
> --
> View this message in context:
> http://postgresql.nabble.com/Strange-error-message-when-reference-non-existent-column-foo-count-tp5831200p5831204.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Well, that clears it up. Thanks!