Greenplum 自定义复合类型 数组

TAG 11

作者

digoal

日期

2017-04-08

标签

PostgreSQL , Greenplum , composite type array

背景

如果你现在要在Greenplum中创建一个复合类型，是不会自动给你创建对应的数组类型的。(而实际上PostgreSQL 在2007年已经支持了这个功能)

Greenplum需要等到5.0，才能使用上它了。

https://github.com/greenplum-db/gpdb/commit/8954a537ee6199a80c38581fd3cd7ed463abbdea

```
Support arrays of composite types
Backport from upstream with this commit:

commit bc8036f  
Author: Tom Lane <tgl@sss.pgh.pa.us>  
Date:   Fri May 11 17:57:14 2007 +0000

    Support arrays of composite types, including the rowtypes of regular tables  
    and views (but not system catalogs, nor sequences or toast tables).  Get rid  
    of the hardwired convention that a type's array type is named exactly "_type",  
    instead using a new column pg_type.typarray to provide the linkage.  (It still  
    will be named "_type", though, except in odd corner cases such as  
    maximum-length type names.)

    Along the way, make tracking of owner and schema dependencies for types more  
    uniform: a type directly created by the user has these dependencies, while a  
    table rowtype or auto-generated array type does not have them, but depends on  
    its parent object instead.

    David Fetter, Andrew Dunstan, Tom Lane

master (#1) 5.0.0-alpha.1 5.0.0-alpha.0
1 parent 5b33bee commit 8954a537ee6199a80c38581fd3cd7ed463abbdea @HaozhouWang HaozhouWang committed on 9 Mar 2016
```

https://www.postgresql.org/docs/8.2/static/sql-createtype.html

Array Types

```
Whenever a user-defined base data type is created, PostgreSQL automatically creates an associated array type,
whose name consists of the base type's name prepended with an underscore.
The parser understands this naming convention, and translates requests for columns of type foo[] into requests for type _foo.
The implicitly-created array type is variable length and uses the built-in input and output functions array_in and array_out.

You might reasonably ask why there is an ELEMENT option, if the system makes the correct array type automatically.
The only case where it's useful to use ELEMENT is when you are making a fixed-length type that happens to be internally an array of a number of identical things,
and you want to allow these things to be accessed directly by subscripting, in addition to whatever operations you plan to provide for the type as a whole.
For example, type name allows its constituent char elements to be accessed this way.
A 2-D point type could allow its two component numbers to be accessed like point[0] and point[1].

Note that this facility only works for fixed-length types whose internal form is exactly a sequence of identical fixed-length fields.
A subscriptable variable-length type must have the generalized internal representation used by array_in and array_out. For historical reasons
(i.e., this is clearly wrong but it's far too late to change it),
subscripting of fixed-length array types starts from zero, rather than from one as for variable-length arrays.
```

PostgreSQL 许愿链接

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.