作者
digoal
日期
2020-11-07
标签
PostgreSQL , pseudo-types : anycompatible, anycompatiblearray, anycompatiblenonarray, anycompatiblerange
背景
PostgreSQL 13 新增pseudo-types anycompatible, anycompatiblearray, anycompatiblenonarray, and anycompatiblerange.
在函数参数、返回结果中使用, 作为任意类型, 可自动根据输入变量自动扩展范围.
例如func(anycompatible,anycompatible) , 调用func(int, int8)时, 第一个参数将自动升级为int8. 提高函数使用便捷性.
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=24e2885ee304cb6a94fdfc25a1a108344ed9f4f7
```
Introduce "anycompatible" family of polymorphic types.
author Tom Lane tgl@sss.pgh.pa.us
Thu, 19 Mar 2020 23:43:11 +0800 (11:43 -0400)
committer Tom Lane tgl@sss.pgh.pa.us
Thu, 19 Mar 2020 23:43:11 +0800 (11:43 -0400)
commit 24e2885ee304cb6a94fdfc25a1a108344ed9f4f7
tree 040c3eead18de05e313c808e07aee262ef2de336 tree | snapshot
parent fab13dc50ba5e7a12b474a7366024681bc169ac8 commit | diff
Introduce "anycompatible" family of polymorphic types.
This patch adds the pseudo-types anycompatible, anycompatiblearray,
anycompatiblenonarray, and anycompatiblerange. They work much like
anyelement, anyarray, anynonarray, and anyrange respectively, except
that the actual input values need not match precisely in type.
Instead, if we can find a common supertype (using the same rules
as for UNION/CASE type resolution), then the parser automatically
promotes the input values to that type. For example,
"myfunc(anycompatible, anycompatible)" can match a call with one
integer and one bigint argument, with the integer automatically
promoted to bigint. With anyelement in the definition, the user
would have had to cast the integer explicitly.
The new types also provide a second, independent set of type variables
for function matching; thus with "myfunc(anyelement, anyelement,
anycompatible) returns anycompatible" the first two arguments are
constrained to be the same type, but the third can be some other
type, and the result has the type of the third argument. The need
for more than one set of type variables was foreseen back when we
first invented the polymorphic types, but we never did anything
about it.
Pavel Stehule, revised a bit by me
Discussion: https://postgr.es/m/CAFj8pRDna7VqNi8gR+Tt2Ktmz0cq5G93guc3Sbn_NVPLdXAkqA@mail.gmail.com
```
Polymorphic Types
Name | Family | Description
anyelement | Simple | Indicates that a function accepts any data type
anyarray | Simple | Indicates that a function accepts any array data type
anynonarray | Simple | Indicates that a function accepts any non-array data type
anyenum | Simple | Indicates that a function accepts any enum data type (see Section 8.7)
anyrange | Simple | Indicates that a function accepts any range data type (see Section 8.17)
anycompatible | Common | Indicates that a function accepts any data type, with automatic promotion of multiple arguments to a common data type
anycompatiblearray | Common | Indicates that a function accepts any array data type, with automatic promotion of multiple arguments to a common data type
anycompatiblenonarray | Common | Indicates that a function accepts any non-array data type, with automatic promotion of multiple arguments to a common data type
anycompatiblerange | Common | Indicates that a function accepts any range data type, with automatic promotion of multiple arguments to a common data type
```
CREATE FUNCTION make_array2(anycompatible, anycompatible)
RETURNS anycompatiblearray AS $$
SELECT ARRAY[$1, $2];
$$ LANGUAGE SQL;
SELECT make_array2(1, 2.5) AS numericarray;
numericarray
{1,2.5}
(1 row)
```
PostgreSQL 14 对一些内置函数进行了改进， 使用自动扩容的任意类型
例如一些窗口函数, 使用会更加便捷.
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5c292e6b90433c760a3e15027646c7b94afd0cdd
Declare lead() and lag() using anycompatible not anyelement.
This allows use of a "default" expression that doesn't slavishly
match the data column's type. Formerly you got something like
"function lag(numeric, integer, integer) does not exist", which
is not just unhelpful but actively misleading.
``` +SELECT lag(ten, four, 0.7) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten; + lag | ten | four +-----+-----+------ + 0 | 0 | 0 + 0 | 0 | 0 + 4 | 4 | 0 + 0.7 | 1 | 1 + 1 | 1 | 1 + 1 | 7 | 1 + 7 | 9 | 1 + 0.7 | 0 | 2 + 0.7 | 1 | 3 + 0.7 | 3 | 3 +(10 rows) +
+SELECT lead(ten * 2, 1, -1.4) OVER (PARTITION BY four ORDER BY ten), ten, four FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten; + lead | ten | four +------+-----+------ + 0 | 0 | 0 + 8 | 0 | 0 + -1.4 | 4 | 0 + 2 | 1 | 1 + 14 | 1 | 1 + 18 | 7 | 1 + -1.4 | 9 | 1 + -1.4 | 0 | 2 + 6 | 1 | 3 + -1.4 | 3 | 3 +(10 rows) + ```
数组操作函数使用更加便捷
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=9e38c2bb5093ceb0c04d6315ccd8975bd17add66
Declare assorted array functions using anycompatible not anyelement.
Convert array_append, array_prepend, array_cat, array_position,
array_positions, array_remove, array_replace, and width_bucket
to use anycompatiblearray.
``` +select array_remove(array[1.0, 2.1, 3.3], 1); + array_remove +-------------- + {2.1,3.3} +(1 row) +
+SELECT ARRAY[1.1] || ARRAY[2,3,4];
+ ?column?
+-------------
+ {1.1,2,3,4}
+(1 row)
+
```
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
