Using functional notation in PostgreSQL queries instead of dot notation

Question

Assuming we have two tables:

1. contacts table having columns: id and name
2. conversations table having columns: id and contact_id (FK to contacts.id)

The following two queries return the same data:

• dot notation: SELECT contacts.name, contacts.id, conversations.id FROM contacts INNER JOIN conversations ON contacts.id = conversations.contact_id;

and

• functional notation: SELECT contacts.name, contacts.id, conversations.id FROM contacts INNER JOIN conversations ON id(contacts) = contact_id(conversations);

For a purely theoretical reason, I want to learn more about the second (more functional) version. What is this syntax called and where can I learn more? Is this syntax in the SQL standard or just PostgreSQL? Is it performant? Why is it not used more widely?

Answer 1

"Functional notation" is an extension to the SQL standard and performance is identical to the standard "attribute notation" (a.k.a. "dot notation").

There are subtle differences how names are resolved. Like: column names take precedence over functions taking the composite type in attribute notation.

Attribute notation only works for functions taking a single parameter. So that's a limited alternative, and the canonical way is to use functional notation for functions (hence the name).

On the other hand, attribute notation is simply shorter (one dot versus two parens), more portable (complies to the standard) and generally the canonical way to table-qualify columns.

Find details in the manual.

Related:

• Store common query as column?

Answer 2

Functional notation is terribly obsolete - it is an artifact from NON SQL era.

Don't use it in production projects. It has no impact on performance - the differences are solved on the parser and analyzer levels, but it does not make any sense with respect to the SQL standard.