[database-devel] Arrays and postgresql-simple

[Joey Adams]

On Mon, Jul 30, 2012 at 9:34 PM, Leon Smith <leon.p.smith at gmail.com> wrote:
> So can the types that can be array elements be extended in any way,  or are
> these types set in stone? (without modifying the PostgreSQL source itself,
> of course...)    And if the array elements are set,  does 9.2 support arrays
> of range types?

(The following is tested with PostgreSQL 8.4.11)

Take a look in the system catalog:

> SELECT oid, typname, typcategory, typarray FROM pg_type;
  oid   |              typname              | typcategory | typarray
--------+-----------------------------------+-------------+----------
     16 | bool                              | B           |     1000
     17 | bytea                             | U           |     1001
     18 | char                              | S           |     1002
     19 | name                              | S           |     1003
     20 | int8                              | N           |     1016
     21 | int2                              | N           |     1005
     22 | int2vector                        | A           |     1006
     23 | int4                              | N           |     1007
--- snip ---
   1000 | _bool                             | A           |        0
   1001 | _bytea                            | A           |        0
   1002 | _char                             | A           |        0
   1003 | _name                             | A           |        0
   1005 | _int2                             | A           |        0
   1006 | _int2vector                       | A           |        0
   1007 | _int4                             | A           |        0
--- snip ---

Every type has a corresponding array type.  When a new type is
created, a corresponding array type is also created in pg_type.
Tables are types, too, and have corresponding array types.

It appears int2vector and oidvector are regular value types, but
overload the ARRAY[] notation and try to act like arrays.  Read on for
more details.

Apparently, int2vector, which is an array-like type, has a
corresponding type _int2vector.  This means you can have an array of
int2vectors:

    > SELECT '{1 2,3 4 5,6 7 8 9}'::int2vector[];
            int2vector
    ---------------------------
     {"1 2","3 4 5","6 7 8 9"}

Notice that each int2vector is independent; no dimension constraining
is happening.  However, the ARRAY syntax appears to be overloaded for
int2vector:

    > SELECT ARRAY[1,2,3] :: int2vector;
     array
    -------
     1 2 3
    (1 row)

But notice the following:

    > SELECT ARRAY['1 2', '3 4 5', '6 7 8']::int2vector[];
              array
    -------------------------
     {"1 2","3 4 5","6 7 8"}
    (1 row)

    > SELECT ARRAY['23 45' :: int2vector, '67 89' :: int2vector];
     array
    -------
     1 0
    (1 row)

The first example works as expected (under our assumption that
int2vector is a regular type and not an array type), but the last
example yields
the wrong type (int2vector instead of int2vector[]) and produces
garbage.  This appears to be the case even with recent Postgres.

If you want to support an array of int2vectors, leave off the
signatures to work around the bug described above.

-Joey