在PostgreSQL查询中使用功能符号代替点符号 [英] Using functional notation in PostgreSQL queries instead of dot notation

问题描述

假设我们有两个表:

1. contacts表具有列:id和name
2. conversations表具有列:id和contact_id(从FK到contacts.id)

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:

• 点表示法: SELECT contacts.name, contacts.id, conversations.id FROM contacts INNER JOIN conversations ON contacts.id = conversations.contact_id;

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

和

• 功能符号: SELECT contacts.name, contacts.id, conversations.id FROM contacts INNER JOIN conversations ON id(contacts) = contact_id(conversations);

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

出于纯粹的理论原因，我想了解更多有关第二个(功能更强)版本的信息.这个语法叫什么，我在哪里可以学到更多?这种语法是在SQL标准中还是在PostgreSQL中?是表演吗?为什么没有更广泛地使用它?

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?

推荐答案

它被称为功能符号"-与标准的属性符号"相对.

It's called "functional notation" - as opposed to the standard "attribute notation".

这是对SQL标准的扩展，并且性能相同.

It's an extension to the SQL standard and performance is identical.

名称解析的方式之间存在细微的差异.像:列名优先于采用点号(属性表示法)复合类型的函数.

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

属性符号(点符号)仅适用于带有单个参数的函数.因此，这是一个有限的选择，规范的方法是对函数使用函数符号(即名称).

Attribute notation (dot 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 (thus 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.

您可以在中找到详细信息手册在这里.

此相关答案有更详细的解释:

This related answer has a more detailed explanation:

• 将常见查询存储为列?

这篇关于在PostgreSQL查询中使用功能符号代替点符号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！