On Wed, Dec 17, 2014 at 3:11 PM, David G Johnston <david.g.johnston@xxxxxxxxx> wrote:
Patrick Krecker wrote
That fact that this is an aggregate function is beside the point - the> 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)?
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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Well, that clears it up. Thanks!
