Extend existing PostgreSQL type

Question

Is it possible to extend an existing datatype in PostgreSQL? Essentially, I want the equivalent of this TypeScript but in SQL:

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

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

Because I have a function that returns all the columns from a meetings table and then the additional two total and hours columns that are computed at query time. Here's what that function looks like:

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;

And right now, I'm getting a type mismatch error because table (like meeting_instances) isn't the same as a table with the meeting_instances columns and the two new hours and total columns. What I want to be able to do is something like this (obviously the syntax below doesn't actually exist... but I'm not sure if there's another way to do this with a similar shorthand syntax):

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)

My current workaround is to create a view and then have that function simply query that view and return the view's type.

Start by disclosing your Postgres version and the actual definition of the involved Postgres tables (`CREATE TABLE ...`). Seems like you have multiple layers of nested composite types. You *can* do that in Postgres. Doesn't mean you should. — Erwin Brandstetter, Sep 05 '21 at 21:27
@ErwinBrandstetter You can nest them, but can you extend them (like a `LIKE` clause on a table definition)? — Bergi, Sep 05 '21 at 21:29
@Bergi: No, you can't. Not in a `RETURNS` clause, not anywhere else. Actually `RETURNS (LIKE tbl)` is not even documented syntax. I am in the process of unravelling the multiple layers in an answer ... — Erwin Brandstetter, Sep 05 '21 at 21:32
@ErwinBrandstetter Yes, that's what I thought. But I don't see why it seems that the OP has "*multiple layers of nested composite types*"? Looking forward to your answer :-) — Bergi, Sep 05 '21 at 21:40
`meeting_instances.time` is already a nested composite type. And we don't know how deep the rabbit hole goes. — Erwin Brandstetter, Sep 05 '21 at 21:43
@ErwinBrandstetter Ah, I had missed that. It might also be a function call tough. We need to see the table/type definitions indeed. — Bergi, Sep 05 '21 at 21:46

Erwin Brandstetter · Accepted Answer · 2021-09-05T22:35:27.737

To your core question:

Is it possible to extend an existing datatype?

No, not possible. Not in the RETURNS clause of CREATE FUNCTION, not anywhere else in PostgreSQL up to version 14.

You can return a field of composite type, plus additional fields of any type. But that's subtly different:

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

Calling that function with:

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

Returns a nested composite type as one of the return columns, like:

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

To decompose the composite type, you could call with:

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

But I wouldn't go there.

My current workaround is to create a view and then have that function simply query that view and return the view's type.

Then just use the view. Don't create an additional function on top of it. Case closed.

If you actually meant to say:

... then use the row type of the view in the RETURNS clause of the function

Then we are back to your question. CREATE VIEW to (implicitly) register the extended row type is a valid option - especially since SELECT * is a convenient syntax shorthand for the case at had. But for starters,

RETURNS TABLE (LIKE meeting_instances)

... isn't documented syntax for CREATE FUNCTION. Nobody should use it. Might be removed without notice in one of the next versions.
The canonical, equivalent, documented syntax is:

RETURNS SETOF meeting_instances

_{(LIKE some_table) is documented syntax for CREATE TABLE. The RETURNS clause of CREATE FUNCTION currently supports the same, but it's not documented, so don't use it.}

Back to your workaround with creating a VIEW. If you have no use for the VIEW, other than registering that extended row type, consider CREATE TYPE instead. Unfortunately, CREATE TYPE does not allow LIKE other_type syntax, either. You have to spell out all columns (attributes) of the composite type. Like:

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

Then you can use:

RETURNS SETOF meeting_plus

Just like you wanted.

But for just the one function, I would use RETURNS TABLE() instead and spell out the return type:

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

Oh, and I wouldn't use "start" and "end" as identifiers in Postgres. Both are reserved words in standard SQL. "end" is absolutely reserved in Postgres and must always be double-quoted.

Extend existing PostgreSQL type

1 Answers1