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

[Peter J. Holzer]

On 2017-08-18 15:57:39 -0500, Justin Pryzby wrote:
> 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:
> > > > 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:

Thanks. I see it now.

hp

-- 
   _  | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| |   | h...@hjp.at | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson 


signature.asc
Description: Digital signature

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

[David G. Johnston]

On Fri, Aug 18, 2017 at 1:47 PM, Peter J. Holzer  wrote:

> So apparently
> ​ ​
> columnname open-parenthesis tablename closed-parenthesis is a specific
> syntactic construct, but I can't find it documented anywhere.


​The documentation linked to speaks mainly in terms of "composite types".
A table IS a composite type (i.e., there is an implicit one of the same
name) for these purposes.​

David J.

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

[Justin Pryzby]

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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Peter J. Holzer]

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.

hp

-- 
   _  | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| |   | h...@hjp.at | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson 


signature.asc
Description: Digital signature

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

[Justin Pryzby]

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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general