Greenplum 自定义复合类型 数组

标签

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.