扩展现有的PostgreSQL类型

Question

在PostgreSQL中是否可以扩展现有数据类型？本质上，我想要的是这段 TypeScript 代码的 SQL 版本：

interface Meeting {
  id: number;
  start: Date;
  end: Date;
  description: string;
}

interface ServiceHour extends Meeting {
  total: number;
  hours: number;
}

因为我有一个从meetings表返回所有列的函数，然后在查询时计算额外的两个total和hours列。以下是该函数的样子：

create or replace function user_hours(org_id text, user_id text)
returns table (like meeting_instances)
as $$
select (sum(hours) over (order by _.instance_time)) total, * from (
  select
    extract(epoch from ((meeting_instances.time).to - (meeting_instances.time).from)) / 60 / 60 as hours, 
    meeting_instances.*
  from meeting_instances inner join relation_people on relation_people.meeting = meeting_instances.id
  where relation_people.user = user_id
  and meeting_instances.org = org_id
  and meeting_instances.instance_time <= current_date
) as _;
$$
language sql stable;

目前我遇到了类型不匹配的错误，因为table (like meeting_instances)与包含meeting_instances列和两个新列hours和total的表不同。 我想要实现的是像下面这样的操作（显然以下语法实际上不存在...但我不确定是否有其他类似的简写语法）：

returns table (total float, hours float, meeting_instances.*)
returns table (total float, hours float) intersect (like meeting_instances)
returns table (total float, hours float) extends (like meeting_instances)

我的当前解决方法是创建一个视图，然后让该函数查询该视图并返回视图的类型。

- Nicholas Chiang

---

首先，披露您的Postgres版本以及涉及的Postgres表的实际定义（CREATE TABLE ...）。看起来您有多层嵌套的复合类型。在Postgres中，您可以这样做。但并不意味着您应该这样做。 - Erwin Brandstetter

@ErwinBrandstetter 你可以嵌套它们，但是你能扩展它们吗（比如在表定义上使用 LIKE 子句）？ - Bergi

@Bergi：不行，你不能这样做。无论是在RETURNS子句中还是其他任何地方都不行。实际上，RETURNS (LIKE tbl)甚至不是文档化的语法。我正在解开一个答案中的多层含义... - Erwin Brandstetter

@ErwinBrandstetter 是的，我也是这么想的。但我不明白为什么似乎OP有“多层嵌套的复合类型”？期待您的回答 :-) - Bergi

meeting_instances.time已经是一个嵌套的复合类型了。而且我们不知道兔子洞有多深。 - Erwin Brandstetter

@ErwinBrandstetter 啊，我错过了那个。它可能也是一个函数调用。我们确实需要看到表/类型定义。 - Bergi

Answer 1

针对您的核心问题:

是否可以扩展现有数据类型?

不行，在PostgreSQL 14版之前，在CREATE FUNCTION的RETURNS子句或任何其他地方都不可能实现。

您可以返回复合类型的字段以及任何类型的附加字段。但这是微妙不同的:

CREATE FUNCTION user_hours_plus( ...)
  RETURNS TABLE (my_meeting meeting_instances, hours numeric, total numeric) ...

使用以下参数调用该函数：

SELECT * FROM user_hours_plus('a', 'b');

返回嵌套复合类型作为返回列之一，例如：

(1,"2017-01-03","2017-01-04", foo) | 123 | 345

为了分解复合类型，你可以使用以下调用：

SELECT (my_meeting).*, hours, total FROM user_hours_plus('a', 'b');

但我不会去那里。

我的当前解决方法是创建一个视图，然后让该函数仅查询该视图并返回视图的类型。

然后只需使用该视图。不要在其上创建其他函数。问题解决。

如果你实际上想说:

... 然后在函数的 RETURNS 子句中使用视图的行类型

那么我们回到了你的问题。使用CREATE VIEW（隐式）注册扩展行类型是一个有效的选项 - 特别是因为 SELECT * 是这种情况下方便的语法缩写。但首先，

RETURNS TABLE (LIKE meeting_instances)

这不是 CREATE FUNCTION 的文档化语法。没有人应该使用它。 它可能会在下一版本中被删除而没有通知。
等价的、规范的文档化语法是：

RETURNS SETOF meeting_instances

_{(LIKE some_table) 是 CREATE TABLE 的已记录语法。目前，CREATE FUNCTION 的 RETURNS 子句也支持此语法，但未记录，因此不要使用它。}

回到你使用创建 VIEW 的解决方法。如果你除了注册扩展行类型以外没有其他用途，可以考虑使用 CREATE TYPE。不幸的是，CREATE TYPE 也不允许使用 LIKE other_type 语法，你必须罗列出该复合类型的所有列（属性）。例如:

CREATE TYPE meeting_plus AS (
   id numeric
 , start date
 , "end" date
 , description text
 , total numeric
 , hours numeric
);

然后您可以使用：

RETURNS SETOF meeting_plus

就像您想要的一样。

但是对于仅一个函数，我会使用RETURNS TABLE()，并拼出返回类型：

RETURNS TABLE (
   id numeric
 , start date
 , "end" date
 , description text
 , total numeric
 , hours numeric)

哦，我不会在Postgres中使用“start”和“end”作为标识符。这两个单词都是标准SQL中的保留字。“end”在Postgres中绝对是保留字，必须始终用双引号括起来。

- Erwin Brandstetter