作者
digoal
日期
2019-09-01
标签
PostgreSQL , 隐式类型转换 , anyelement , anytype , anydata , anyarray , ommontype , commonarray
背景
很多地方存在隐式类型转换的需求，例如操作符的操作数类型转换，变量赋值的类型转换，函数参数的类型转换等。
postgresql提供了几种任意类型作为自定义函数的传入类型（anyelement, any, anyarray等），方便用户编写通用函数。
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)
当自定义plpgsql函数有多个anyxxx类型参数时，调用时必须类型一致，不能有差异。没有自动转换类型，使用不方便。
目前有个patch，社区还在讨论，主要纠结的是新引入的any类型名字叫什么？相信很快会引入主干。
希望达到的效果
```
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)
```
any类型隐式类型转换patch讨论
https://www.postgresql.org/message-id/CAFj8pRCZVo_xoW0cfxt=mmgjXKBgr3Gm1VMGL_zx9wDRHmm6Cw@mail.gmail.com
Hi
I am sending a proof concept. Current implementation is not suboptimal - I
wrote this code for demonstration of current issues, and checking possible
side effects of changes in this patch.
The basic problem is strong restrictive implementation of polymorphic types
- now these types doesn't allow any cast although it is possible. It can be
changed relatively simply I though (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 our functions like COALESCE are not too restrictive and allow to use
types from same category.
```
postgres=# select coalesce(1,1.1);
coalesce
1复制
(1 row)
```
With attached patch the polymorphic functions use same mechanism as our
buildin functions. It is applied on ANYARRAY, 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)
Comments, notices, ... ?
Regards
Pavel
https://www.postgresql.org/message-id/flat/CAFj8pRDna7VqNi8gR+Tt2Ktmz0cq5G93guc3Sbn_NVPLdXAkqA@mail.gmail.com
Hi,
the possibility to use polymorphic types is a specific interesting
PostgreSQL feature. The polymorphic types allows to use almost all types,
but when some type is selected, then this type is required strictly without
possibility to use some implicit casting.
So if I have a fx(anyelement, anyelement), then I can call function fx with
parameters (int, int), (numeric, numeric), but I cannot to use parameters
(int, numeric). The strict design has sense, but for few important cases is
too restrictive. We are not able to implement (with plpgsql) functions like
coalesce, greatest, least where all numeric types can be used.
Alternative solution can be based on usage "any" type. But we can work with
this type only from "C" extensions, and there is some performance
penalization due dynamic casting inside function.
Four years ago I proposed implicit casting to common type of arguments with
anyelement type.
https://www.postgresql.org/message-id/CAFj8pRCZVo_xoW0cfxt%3DmmgjXKBgr3Gm1VMGL_zx9wDRHmm6Cw%40mail.gmail.com
My proposal was rejected, because it introduce compatibility issues.
Now I have a solution that doesn't break anything. With two new polymorphic
types: commontype and commontypearray we can write functions like coalesce,
greatest, ..
More, these types are independent on current polymorphic types - and can be
used with current polymorphic types together 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
and commontype and anyelement types can be really independent.
Comments, notes?
Regards
参考
https://www.postgresql.org/message-id/CAFj8pRCZVo_xoW0cfxt=mmgjXKBgr3Gm1VMGL_zx9wDRHmm6Cw@mail.gmail.com
https://www.postgresql.org/message-id/flat/CAFj8pRDna7VqNi8gR+Tt2Ktmz0cq5G93guc3Sbn_NVPLdXAkqA@mail.gmail.com
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
