使用 Postgres 类型

在本系列的第 2 集中，我注意到我用来编写 Postgres 函数的语言共享一个公共类型系统。我花了一段时间才理解类型在 Postgres 函数的上下文中是如何工作的，这些函数可以返回记录集并可以与表和物化视图交互。创建函数 notes_for_user_in_group( _userid text, _groupid text) 返回 setof 注释作为 $$ begin return query select * from annotation where userid = concat('acct:', _userid) and groupid = _groupid;结尾; $$ 语言 plpgsql;在这种情况下，已经定义了管理返回集的类型：它是注释表的模式。该函数返回匹配用户 ID 和组 ID 的记录。我现在可以找到我最近注释的文档的 URL。您可能想知道为什么函数的参数以下划线为前缀。这是因为函数中使用的变量可能与表中的列名冲突。由于我们的列名都没有以下划线开头，因此这是一个方便的区分符。假设函数的签名改为： 错误：列引用“userid”不明确第 2 行：其中 userid = concat('acct:', userid) ^ DETAIL：它可以引用 PL/pgSQL 变量或表列。该表具有与其同名变量冲突的 userid 和 groupid 列。所以对于结合变量和数据库值的函数，我用下划线作为变量名的前缀。

可以在任何 SQL SELECT 上下文中调用返回集合的函数。在上面的例子中，上下文是 psql，Postgres 强大且多才多艺的 REPL（读取-评估-打印循环）。对于不同上下文的示例，让我们在物化视图中缓存函数的结果集。使用数据创建物化视图 public_notes_for_judell as ( select * from notes_for_user_in_group('[email protected]', '__world__') order by created desc )；视图的类型是隐式注解；它的模式与上面显示的模式相匹配；从视图中选择 target_uri 相当于从函数 notes_for_user_in_group 返回的 setof 注释中选择 target_uri。虽然它显示得更快！每次选择函数的结果集时，都必须运行包装查询。对于这个可能需要几秒钟的特定示例。创建视图所需的时间相同。但是一旦完成，您就可以在几毫秒内选择其内容。现在让我们定义一个函数，通过报告每个注释文档的注释计数来优化 notes_for_user_in_group。创建函数 annotated_docs_for_user_in_group( _userid text, _groupid text) 返回表 ( count bigint, userid text, groupid text, url text ) as $$ begin return query select count(n.*) as anno_count, n.userid, n.groupid, n .target_uri from notes_for_user_in_group(_userid, _groupid) n group by n.userid, n.groupid, n.target_uri order by anno_count desc;结尾; $$ 语言 plpgsql;该函数不返回某个命名类型的集合，而是返回一个匿名表。我将集合返回函数调用 notes_for_user_in_group 别名为 n 并使用别名来限定所选列的名称。这避免了另一个命名冲突。如果在函数体中写入 userid 而不是 n.userid 然后调用它，Postgres 再次抱怨冲突。

错误：列引用“userid”不明确第 3 行：userid，^ 详细信息：它可以引用 PL/pgSQL 变量或表列。计数 |用户名 |组名 | target_uri -------+---------------------------------------- ---- 516 | [email protected] | __世界__ | http://shakespeare.mit.edu/macbeth/full.html 73 | [email protected] | __世界__ | https://www.independent.co.uk/news/world/asia/india-floods-bangladesh-nepal-deaths-millions-homeless-latest-news-updates-a7919006.html 51 | [email protected] | __世界__ | https://www.usatoday.com/story/news/nation-now/2017/06/16/coconut-oil-isnt-healthy-its-never-been-healthy/402719001/ 创建物化视图 url_counts_for_public_notes_by_judell as ( select *来自 annotated_docs_for_user_in_group( '[email protected]', '__world__' ) ) 和数据；当您在 psql 中使用 \d 命令询问该视图的定义时： Column |类型 ---------+-------- 计数 | bigint 用户名 |文本组ID |文字网址 | text 幕后 Postgres 从函数返回的匿名表中创建了这个定义。要修改函数以使其使用命名类型，请首先创建类型。

现在我们可以在函数中使用该命名类型。由于我们正在重新定义函数，因此首先将其删除。错误：无法删除函数 annotated_docs_for_user_in_group(text,text) 因为其他对象依赖于它详细信息：物化视图 url_counts_for_public_notes_by_judell 依赖于函数 annotated_docs_for_user_in_group(text,text) 提示：使用 DROP ... CASCADE 也删除依赖对象。当函数的签名更改时，必须重新创建依赖于函数的视图。我将在以后的关于集合返回函数的一集中详细说明这一点，这些函数将其结果动态缓存在物化视图中。现在，由于我们刚刚创建的视图是一个人为的一次性视图，只需按照 Postgres 的建议使用 CASCADE 将其与函数一起删除。现在我们可以重新创建一个返回 setof annotated_docs_for_user_in_group 而不是匿名表的函数版本(...) create function annotated_docs_for_user_in_group( _userid text, _groupid text) 返回 setof annotated_docs_for_user_in_group as $$ begin return query select count(n.*) as anno_count, n.userid, n.groupid, n.target_uri from notes_for_user_in_group(_userid, _groupid) n group by n.userid, n.groupid, n.target_uri order by anno_count desc;结尾; $$ 语言 plpgsql;结果与上述相同。那么为什么要这样做呢？在很多情况下，我不会。声明类型是额外的开销。就像视图可以依赖于函数一样，函数也可以依赖于类型。要了解为什么您可能不想要这样的依赖关系，假设我们还想跟踪每个 URL 的最新注释。创建类型 annotated_docs_for_user_in_group as ( count bigint, userid text, groupid text, url text, most_recent_note timestamp );

错误：无法删除类型 annotated_docs_for_user_in_group 因为其他对象依赖于它详细信息：函数 annotated_docs_for_user_in_group(text,text,text) 依赖于类型 annotated_docs_for_user_in_group 提示：使用 DROP ... CASCADE 也删除依赖对象。要重新定义类型，您必须进行级联删除，然后重新创建依赖于类型的函数。如果这些视图中的任何一个依赖于删除的函数，则删除也会级联到它们，并且它们也必须重新创建。这就是为什么我经常编写返回 table(...) 而不是 setof TYPE 的函数。在动态语言中，使用无类型的值包很方便；我发现在 Postgres 中编写函数时也是如此。但是，有时声明和使用类型很有用。根据我目前的经验，当您发现自己在几个相关函数中编写相同的返回表（...）语句时，在 Postgres 中这样做是最有意义的。假设我们想要一个函数，将 annotated_docs_for_user_in_group 的结果组合到一组用户中。创建函数 annotated_docs_for_users_in_group(_userids text[], _groupid text) 返回 setof annotated_docs_for_user_in_group as $$ begin return query with userids as ( select unnest(_userids) as userid ) select a.* from userids u join annotated_docs_for_user_group) a.userid = concat('acct:', u.userid);结尾; $$ 语言 plpgsql;这个新函数使用 SQL WITH 子句创建一个公用表表达式 (CTE)，该表达式将入站的 userid 数组转换为一个临时的类似表的对象，名为 userids，每行一个 userid。新函数的包装 SQL 然后将该 CTE 连接到从 annotated_docs_for_user_in_group 返回的集合并返回连接结果。 （您也可以通过创建循环变量并遍历数组以累积结果，以更程序化的方式执行此操作。早先我使用了这种方法，但在 Postgres 函数的上下文中，我开始更喜欢更纯粹的类似 SQL 的方法面向集合的风格。）在两个函数之间共享一个公共类型使它们更易于编写和阅读。更重要的是，它将它们彼此联系起来，并与从它们衍生的所有观点联系起来。如果我决定将 most_recent_note 添加到类型中，Postgres 将要求我调整所有依赖的函数和视图，以便保持一致。这可能是一个至关重要的保证，正如我们将在未来的一集中看到的那样，它是高级缓存机制的关键推动者。