扩展现有的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)

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

Answer 1

你的核心问题:

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

没有不可能。在CREATE FUNCTION的RETURNS子句中没有，在PostgreSQL版本14之前的任何地方都没有。

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

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 *是对case at had的方便语法简写。但是对于初学者来说，

RETURNS TABLE (LIKE meeting_instances)

…没有记录CREATE FUNCTION的语法。没有人应该使用它。可能在下一个版本中不经通知就被删除了。
规范的、等价的、文档化的语法是:

RETURNS SETOF meeting_instances

_{(LIKE some_table)是CREATE TABLE的文档语法。CREATE FUNCTION的RETURNS子句目前支持相同的功能，但没有文档记录，所以不要使用它。}

回到创建VIEW的解决方案。如果您不需要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)

哦，我不会用"start"one_answers";以何种;作为Postgres中的标识符。这两个词在标准SQL中都是保留词。"以何种在Postgres中是绝对保留的，必须总是双引号。