Tuesday, March 31, 2020

Postgres 13, new family of polymorphic types - anycompatible

One very interesting PostgerSQL's feature are polymorphic types. It's interesting mainly for people who writes extensions.

Example: I can write own isnull function:
CREATE OR REPLACE FUNCTION isnull(anyelement, anyelement)
RETURNS anyelement AS $$
SELECT coalesce($1, $2)
$$ LANGUAGE sql;

postgres=# SELECT public.isnull(NULL, 1);
┌────────┐
│ isnull │
╞════════╡
│      1 │
└────────┘
(1 row)

postgres=# SELECT public.isnull(NULL, CURRENT_DATE);
┌────────────┐
│   isnull   │
╞════════════╡
│ 2020-03-31 │
└────────────┘
(1 row)

-- but
postgres=# SELECT public.isnull(1, 1.1);
ERROR:  function public.isnull(integer, numeric) does not exist
LINE 1: SELECT public.isnull(1, 1.1);
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

It is working for all supported types. One and big limit of type family any* is rigidity. It is working for all types, but for previous example, first and second parameter has to have exactly same types. Similar buildin functions are more tolerant - usually requires same type category only. This limit should be removed with new family of polymorphic types anycompatible. When there are more arguments with this type, then most common type is calculated and all arguments of anycompatible are casted to selected most common type:

CREATE OR REPLACE FUNCTION isnull(anycompatible, anycompatible)
RETURNS anycompatible AS $$
SELECT coalesce($1, $2)
$$ LANGUAGE sql;

postgres=# SELECT public.isnull(1, 1.1);
┌────────┐
│ isnull │
╞════════╡
│      1 │
└────────┘
(1 row)

postgres=# SELECT public.isnull(NULL, 1.1);
┌────────┐
│ isnull │
╞════════╡
│    1.1 │
└────────┘
(1 row)

Now, it is working as expected.

This feature is interesting mainly for authors of extensions that has to emulate some different databases (like Orafce or similar).

2 Comments:

At May 21, 2020 at 5:57 AM , Blogger Esteban Zimanyi said...

Many thanks for this great extensibility you wrote for PG 13. However I wonder whether it is possible to go a little bit further in this direction.

We are developing MobilityDB
https://github.com/ULB-CoDE-WIT/MobilityDB
a PostgreSQL/PostGIS extension to work with mobility data. MobilityDB defines temporal types. Examples of such types are temporal points representing, e.g., the GPS coordinates of a moving car or temporal floats representing, e.g., the speed of the previous car. Conceptually, temporal types are functions from the time domain to a base type, e.g,, giving at each timestamp the location of a moving car, which is a PostGIS point. Currently, MobilityDB defines the following temporal types tbool, tint, tfloat, ttext, tgeompoint and tgeogpoint, which correspond to the base types bool, int, float, text, geometry and geography.

There is many functionality common to ANY temporal point independent of its base type (e.g., PostGIS geometry for a point or PostgreSQL float) so at the SQL level we to define the same function many times

CREATE FUNCTION function(tbool) ... AS 'MODULE_PATHNAME', 'function_in_c' ...
CREATE FUNCTION function(tint) ... AS 'MODULE_PATHNAME', 'function_in_c' ...
CREATE FUNCTION function(tfloat) ... AS 'MODULE_PATHNAME', 'function_in_c' ...
CREATE FUNCTION function(ttext) ... AS 'MODULE_PATHNAME', 'function_in_c' ...
CREATE FUNCTION function(tgeompoint) ... AS 'MODULE_PATHNAME', 'function_in_c' ...
CREATE FUNCTION function(tgeogpoint) ... AS 'MODULE_PATHNAME', 'function_in_c' ...

which all call the same C function.

What would be great is to be able to define a pseudo type 'anytemporal' so that
* tbool, tint, .... are all compatible with anytemporal
* Define only once each function as follows

CREATE FUNCTION function(anytemporal) ... AS 'MODULE_PATHNAME', 'function_in_c' ...

Is there any change to see that functionality in PostgreSQL ? That would allow us toremove thousands of lines of SQL code in MobilityDB !





 
At May 21, 2020 at 5:58 AM , Blogger Esteban Zimanyi said...

Many thanks for this great extensibility you wrote for PG 13. However I wonder whether it is possible to go a little bit further in this direction.

We are developing MobilityDB
https://github.com/ULB-CoDE-WIT/MobilityDB
a PostgreSQL/PostGIS extension to work with mobility data. MobilityDB defines temporal types. Examples of such types are temporal points representing, e.g., the GPS coordinates of a moving car or temporal floats representing, e.g., the speed of the previous car. Conceptually, temporal types are functions from the time domain to a base type, e.g,, giving at each timestamp the location of a moving car, which is a PostGIS point. Currently, MobilityDB defines the following temporal types tbool, tint, tfloat, ttext, tgeompoint and tgeogpoint, which correspond to the base types bool, int, float, text, geometry and geography.

There is many functionality common to ANY temporal point independent of its base type (e.g., PostGIS geometry for a point or PostgreSQL float) so at the SQL level we to define the same function many times

CREATE FUNCTION function(tbool) ... AS 'MODULE_PATHNAME', 'function_in_c' ...
CREATE FUNCTION function(tint) ... AS 'MODULE_PATHNAME', 'function_in_c' ...
CREATE FUNCTION function(tfloat) ... AS 'MODULE_PATHNAME', 'function_in_c' ...
CREATE FUNCTION function(ttext) ... AS 'MODULE_PATHNAME', 'function_in_c' ...
CREATE FUNCTION function(tgeompoint) ... AS 'MODULE_PATHNAME', 'function_in_c' ...
CREATE FUNCTION function(tgeogpoint) ... AS 'MODULE_PATHNAME', 'function_in_c' ...

which all call the same C function.

What would be great is to be able to define a pseudo type 'anytemporal' so that
* tbool, tint, .... are all compatible with anytemporal
* Define only once each function as follows

CREATE FUNCTION function(anytemporal) ... AS 'MODULE_PATHNAME', 'function_in_c' ...

Is there any change to see that functionality in PostgreSQL ? That would allow us toremove thousands of lines of SQL code in MobilityDB !

 

Post a Comment

Subscribe to Post Comments [Atom]

<< Home