Clever Geek Handbook

Using functional notation in PostgreSQL queries instead of dot notation

Let's assume we have two tables:

  • contacts

    a table with columns: id

    andname

  • conversations

    table with columns: id

    and contact_id

    (from FK to contacts.id

    )

The following two queries return the same data:

  • point designation: 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 know more about the second (more functional) version. What is this syntax called and where can I find out more? Is this syntax a SQL standard or just PostgreSQL? Is this a performer? Why isn't it used more widely?

+3


source to share


2 answers


It is called "functional notation" - as opposed to standard "attribute notation".

This extension to the SQL standard and performance are identical.

There are subtle differences in how names are resolved. For example: column names take precedence over functions that take a composite type in dot notation (attribute notation).

Attribute notation (dot notation) only works for functions that take one parameter. So the limited alternative and canonical way is to use functional notation for functions (thus name).



On the other hand, attribute notation is simply shorter (one dot versus two parens), more portable (conforming to the standard), and usually the canonical path to table columns.

See the manual here for details .

This answer has a more detailed explanation:

+1


source


Functional notation is horribly outdated - it's an artifact from the NON SQL era.



Don't use it in production projects. This does not affect performance - the differences are resolved at the parser and parser levels, but it doesn't make any sense in relation to the SQL standard.

+1


source






More articles:


All Articles