How to define template types in PostgreSQL

From: Esteban Zimanyi <esteban(dot)zimanyi(at)ulb(dot)be>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: How to define template types in PostgreSQL
Date: 2023-01-07 09:31:55
Message-ID: CAPqRbE4Kymd2X6DncoLRjLG+2+7cPvNddU1UFfQTOy1wSnL1Zg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Dear all

MobilityDB (https://github.com/MobilityDB/MobilityDB) defines at the C
level four template types: Set, Span, SpanSet, and Temporal. The type Set
is akin to PostgreSQL's ArrayType restricted to one dimension, but enforces
the constraint that sets do not have duplicates, the types Span and SpanSet
are akin to PostgreSQL's RangeType and MultirangeType but enforce the
constraints that span types are of fixed length and that empty spans and
infinite bounds are not allowed, and the typeTemporal is used to
manipulate time-varying values.

These template types need to be instantiated at the SQL level with base
types (int, bigint, float, timestamptz, text, ...) and because of this,
MobilityDB needs to define numerous SQL functions that all call the same
function in C. Taking as example the Set type, we need to define, e.g.,

CREATE FUNCTION intset_eq(intset, intset) RETURNS bool AS
'MODULE_PATHNAME', 'Set_eq' ...
CREATE FUNCTION bigintset_eq(bigintset, bigintset) RETURNS bool AS
'MODULE_PATHNAME', 'Set_eq' ...
CREATE FUNCTION floatset_eq(floatset, floatset) RETURNS bool AS
'MODULE_PATHNAME', 'Set_eq' ...
CREATE FUNCTION textset_eq(textset, textset) RETURNS bool AS
'MODULE_PATHNAME', 'Set_eq' ...
...

CREATE FUNCTION intset_ne(intset, intset) RETURNS bool AS
'MODULE_PATHNAME', 'Set_ne' ...
CREATE FUNCTION bigintset_ne(bigintset, bigintset) RETURNS bool AS
'MODULE_PATHNAME', 'Set_ne' ...
CREATE FUNCTION floatset_ne(floatset, floatset) RETURNS bool AS
'MODULE_PATHNAME', 'Set_ne' ...
CREATE FUNCTION textset_ne(textset, textset) RETURNS bool AS
'MODULE_PATHNAME', 'Set_ne' ...
...

In the case of arrays, ranges, and multiranges, PostgreSQL avoids this
redundancy using pseudo-types such as anyarray, anyrange, anymultirange, ...

Is there a possibility that we can also define pseudo types such as anyset,
anyspan, anyspanset, anytemporal, .... ?

This will considerably reduce the number of SQL functions to define.
Currently, given the high number of functions in MobilityDB, creating the
extension takes a loooong time ....

Regards

Esteban

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message David Rowley 2023-01-07 09:46:43 Allow DISTINCT to use Incremental Sort
Previous Message Dilip Kumar 2023-01-07 08:55:31 Re: Perform streaming logical transactions by background workers and parallel apply