Re: Count column with name 'count' returns multiple rows. Why?

On Fri, Aug 18, 2017 at 10:47:37PM +0200, Peter J. Holzer wrote:
> On 2017-08-18 06:37:15 -0500, Justin Pryzby wrote:
> > On Fri, Aug 18, 2017 at 01:01:45PM +0200, Rob Audenaerde wrote:
> > > I don't understand why this query:
> > >
> > > select count(base.*) from mytable base;
> > >
> > > does return multiple rows.
> > >
> > > select count(1) from mytable base;
> > >
> > > returns the proper count.
> > >
> > > There is a column with the name 'count'.
> > >
> > > Can anyone please explain this behaviour?
> >
> > https://www.postgresql.org/docs/9.6/static/sql-expressions.html#SQL-EXPRESSIONS-FUNCTION-CALLS
> > https://www.postgresql.org/docs/9.6/static/rowtypes.html#ROWTYPES-USAGE
>
> Maybe I overlooked it, but I don't see anything in those pages which
> explains why «count» is parsed as a column name in the first example and
> as a function name in the second.
>
> Nor do I see what «count(base.*)» is supposed to mean. It seems to be
> completely equivalent to just writing «count», but the part in
> parentheses is not ignored: It has to be either the table name or the
> table name followed by «.*». Everything else I tried either led to a
> syntax error or to «count» being recognized as a function. So apparently
> columnname open-parenthesis tablename closed-parenthesis is a specific
> syntactic construct, but I can't find it documented anywhere.

| Another special syntactical behavior associated with composite values is that
|we can use functional notation for extracting a field of a composite value. The
|simple way to explain this is that the notations field(table) and table.field
|are interchangeable. For example, these queries are equivalent:

| Tip: Because of this behavior, it's unwise to give a function that takes a
|single composite-type argument the same name as any of the fields of that
|composite type. If there is ambiguity, the field-name interpretation will be
|preferred, so that such a function could not be called without tricks. One way
|to force the function interpretation is to schema-qualify the function name,
|that is, write schema.func(compositevalue).

pryzbyj=# select base.count from s91 base;
count
-------
1
2
3
(3 rows)

pryzbyj=# select pg_catalog.count(base.*) from s91 base;
count | 3