Implicit Type Conversion of Any Types and Any Arrays on PostgreSQL

By digoal

Background

You need implicit type conversion in many operations, such as the type conversion of the operands of operators, the variable assignment, and the function parameters.

PostgreSQL provides several any types (any anyelement, anyarray, and so on) as the input types of user-defined functions to facilitate the compilation of general functions.

postgres=# \dT any*  
                                        List of data types  
   Schema   |    Name     |                              Description                                
------------+-------------+-----------------------------------------------------------------------  
 pg_catalog | "any"       | pseudo-type representing any type  
 pg_catalog | anyarray    | pseudo-type representing a polymorphic array type  
 pg_catalog | anyelement  | pseudo-type representing a polymorphic base type  
 pg_catalog | anyenum     | pseudo-type representing a polymorphic base type that is an enum  
 pg_catalog | anynonarray | pseudo-type representing a polymorphic base type that is not an array  
 pg_catalog | anyrange    | pseudo-type representing a polymorphic base type that is a range  
(6 rows)

When a user-defined PL/pgSQL function has multiple “anyxxx” parameters, they have to be of the same type for the calling process. It is inconvenient to use these parameters since the type is not automatically converted.

At present, the community is discussing a patch, and the crux is what the name of the newly introduced “anyxxx” parameter should be. We believe that it will be announced soon.

Ideal effect:

CREATE OR REPLACE FUNCTION public.foo1(anyelement, anyelement)  
 RETURNS anyelement  
 LANGUAGE sql  
AS $function$  
SELECT $1 + $2;  
$function$  
  
CREATE OR REPLACE FUNCTION public.foo2(anyelement, anyelement)  
 RETURNS anyarray  
 LANGUAGE sql  
AS $function$  
SELECT ARRAY[$1, $2]  
$function$  
  
CREATE OR REPLACE FUNCTION public.foo3(VARIADIC anyarray)  
 RETURNS anyelement  
 LANGUAGE sql  
AS $function$  
SELECT min(v) FROM unnest($1) g(v)  
$function$  
  
postgres=# select foo1(1,1.1), foo2(1,1.1), foo3(1.1,2,3.1);  
 foo1 |  foo2   | foo3  
------+---------+------  
  2.1 | {1,1.1} |  1.1  
(1 row)

Discussion: Patch for Implicit Type Conversion of “Anyxxx” Parameters

https://www.postgresql.org/message-id/CAFj8pRCZVo_xoW0cfxt=mmgjXKBgr3Gm1VMGL_zx9wDRHmm6Cw@mail.gmail.com

The following is a proof concept. The current implementation is not suboptimal — this code is written to demonstrate the current issues and check possible side effects of changes in this patch.

The fundamental problem is the strong restrictive implementation of polymorphic types — now, these types don't allow any cast although it is possible. It can be changed relatively in a simple manner (after we implemented variadic functions).

CREATE OR REPLACE FUNCTION public.foo1(anyelement, anyelement)  
 RETURNS anyelement  
 LANGUAGE sql  
AS $function$  
SELECT $1 + $2;  
$function$  
  
CREATE OR REPLACE FUNCTION public.foo2(anyelement, anyelement)  
 RETURNS anyarray  
 LANGUAGE sql  
AS $function$  
SELECT ARRAY[$1, $2]  
$function$

Now, polymorphic functions don't allow some natively expected calls:

postgres=# select foo1(1,1);  
 foo1  
------  
    2  
(1 row)  
  
postgres=# select foo1(1,1.1);  
ERROR:  function foo1(integer, numeric) does not exist  
LINE 1: select foo1(1,1.1);  
               ^  
HINT: No function matches the given name and argument types. You might need to add explicit type casts.  
  
postgres=# select foo2(1,1);  
 foo2  
-------  
 {1,1}  
(1 row)  
  
postgres=# select foo2(1,1.1);  
ERROR:  function foo2(integer, numeric) does not exist  
LINE 1: select foo2(1,1.1);  
               ^  
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
  
CREATE OR REPLACE FUNCTION public.foo3(VARIADIC anyarray)  
 RETURNS anyelement  
 LANGUAGE sql  
AS $function$  
SELECT min(v) FROM unnest($1) g(v)  
$function$  
  
postgres=# SELECT foo3(1,2,3);  
 foo3  
------  
    1  
(1 row)  
  
postgres=# SELECT foo3(1,2,3.1);  
ERROR:  function foo3(integer, integer, numeric) does not exist  
LINE 1: SELECT foo3(1,2,3.1);  
               ^  
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Some of our functions like COALESCE are not too restrictive and allow using types from the same category.

postgres=# select coalesce(1,1.1);  
 coalesce  
----------  
        1  
(1 row)

With the attached patch, the polymorphic functions use the same mechanism as our built-in functions. It is applied on anyarray and anyelement types only.

postgres=# select foo1(1,1.1), foo2(1,1.1), foo3(1.1,2,3.1);  
 foo1 |  foo2   | foo3  
------+---------+------  
  2.1 | {1,1.1} |  1.1  
(1 row)

https://www.postgresql.org/message-id/flat/CAFj8pRDna7VqNi8gR+Tt2Ktmz0cq5G93guc3Sbn_NVPLdXAkqA@mail.gmail.com

The possibility to use polymorphic types is a specific interesting PostgreSQL feature. The polymorphic types allow using almost all types. But when some type is selected, this type is required strictly, allowing the possibility to use some implicit casting.

So if there is an fx(anyelement, anyelement), then you can call function fx parameters (int, int), (numeric, numeric), but cannot use parameters (int, numeric). The strict design makes sense, but is too restrictive for few important cases. We are unable to implement (with PL/pgSQL) functions like coalesce, greatest, and least where you can use all numeric types.

An alternative solution can be based on using "any" type. But we can work with this type only from "C" extensions, and there is some performance penalty due to dynamic casting inside the function.

Four years ago, implicit casting to common type of arguments in anyelement type was proposed.

https://www.postgresql.org/message-id/CAFj8pRCZVo_xoW0cfxt%3DmmgjXKBgr3Gm1VMGL_zx9wDRHmm6Cw%40mail.gmail.com

The proposal was rejected because it introduced compatibility issues.

Now, we have a solution that doesn't break anything. With two new polymorphic types — commontype and commontypearray — we can write functions like coalesce, greatest, and so on.

Moreover, these types are independent of current polymorphic types — and you can use them together with the current polymorphic types to cover some new use cases.

CREATE OR REPLACE FUNCTION fx(anyelement, commontype, anyelement,  
commontype)  
RETURNS commontype

Or,

CREATE OR REPLACE FUNCTION fx(anyelement, commontype, anyelement,  
commontype)  
RETURNS anyelement

Additionally, commontype and anyelement types can be really independent.

Community

Implicit Type Conversion of Any Types and Any Arrays on PostgreSQL

Background

Discussion: Patch for Implicit Type Conversion of “Anyxxx” Parameters

References

Read previous post:

Read next post:

digoal

You may also like

Comments

digoal

Related Products

PolarDB for PostgreSQL

AnalyticDB for PostgreSQL

ApsaraDB RDS for PostgreSQL

Database for FinTech Solution