Re: array support patch phase 1 patch - Mailing list pgsql-patches

Joe Conway wrote:
> I just found a huge bogosity in this patch (ExecEvalArray)-- I must have
> been tired, and my test cases were obviously too simple ;(
>
> I'm working on a fix right now, but I guess I ought to add some good
> test cases to the regression suite before submitting again. Please
> retract this patch for the moment, and I'll send in the fix with
> regression test adjustments in the next few days.

Here's an updated patch that fixes the above mentioned issues. I also
added significantly more functionality.

It needs a good review, but everything seems to work, it passes all
regression tests**, and I think it is ready to apply (I needed to change
some of the regression tests, so someone please take a close look at
those too).

**except horology due to DST, and for some reason that I don't yet
understand, /expected/misc.out loses my changes every time I update from
CVS -- is misc.out autogenerated?

It covers the following:
----------------------------------------------------------------------
1. Support for polymorphic functions, accepting and returning ANYARRAY
    and ANYELEMENT datatypes that are "tied" to each other and resolved
    to an actual type at runtime. This also includes the ability to
    define aggregates using the polymorphic functions.

2. Array handling functions:
    - singleton_array(ANYELEMENT) returns ANYARRAY
    - array_append(ANYARRAY, ANYELEMENT) returns ANYARRAY
    - array_prepend(ANYELEMENT, ANYARRAY) returns ANYARRAY
    - array_accum(ANYARRAY, ANYELEMENT) returns ANYARRAY
    - array_assign(ANYARRAY, int, ANYELEMENT) returns ANYARRAY
    - array_subscript(ANYARRAY, int) returns ANYELEMENT
    - array_cat(ANYARRAY, ANYARRAY) returns ANYARRAY

3. Grammar and underlying support for the following (examples):

    create table foo(f1 integer ARRAY);
    create table foo(f1 integer ARRAY[]);
    create table foo(f1 integer ARRAY[x]);
    create table foo(f1 integer ARRAY[][]);   (and more [] etc)
    create table foo(f1 integer ARRAY[x][y]); (and more [] etc)

    select ARRAY[1,2,3];
    select ARRAY[[1,2,3],[4,5,6]];
    select ARRAY[ARRAY[1,2,3],ARRAY[4,5,6]];
    ...etc up to 6 dimensions

    select ARRAY[1,2] || 3;
    select 0 || ARRAY[1,2];
    select ARRAY[1,2] || ARRAY[3,4];
    select ARRAY[1,2] || ARRAY[[3,4],[5,6]];
    select ARRAY[[1,2],[3,4]] || ARRAY[5,6];
    select ARRAY(select oid from pg_class order by relname);

    select ARRAY[[1,2,3],[4,5,6]]::float8[];
    select ARRAY[[1,2,3],[4,5,6]]::text[];
    select CAST(ARRAY[[1,2,3],[4,5,6]] AS float8[]);

4. Duplicated contrib/array functionality (and then some) in the
    backend using polymorphic functions and operators. Also cleared
    out the datatype specific "=" operators. Now there is just one
    polymorphic one. If the patch is applied, I think contrib/array can
    be safely retired. Examples:

    SELECT ARRAY[1,2,3] *= 2 AS "TRUE";
    SELECT ARRAY[1,2,3] *<> 4 AS "TRUE";
    SELECT ARRAY[1,2,3] *< 2 AS "TRUE";
    SELECT ARRAY[1,2,3] *> 2 AS "TRUE";
    SELECT ARRAY[1,2,3] *<= 1 AS "TRUE";
    SELECT ARRAY[1,2,3] *>= 3 AS "TRUE";
    SELECT ARRAY['abc','def','qwerty'] *~ 'wer' AS "TRUE";
    SELECT ARRAY['abc','def','qwerty'] *!~ 'xyz' AS "TRUE";
    SELECT ARRAY['abc','def','qwerty'] *~~ '_wer%' AS "TRUE";
    SELECT 'hello' *~~ ARRAY['abc','def','_ell%'] AS "TRUE";
    SELECT ARRAY['abc','def','qwerty'] *!~~ '%xyz%' AS "TRUE";

5. Side note: I added ANYARRAY1 and ANYELEMENT1 in this version. I
    needed ANYARRAY1 for polymorphic array coercion, but did not add
    support for it to be used in any other way. I added ANYELEMENT1
    only for symmetry, and did not use it at all.

Still needs to be done (in roughly this order):
----------------------------------------------------------------------
1. Functions:
    - str_to_array(str TEXT, delim TEXT) returns TEXT[]
    - array_to_str(array ANYARRAY, delim TEXT) returns TEXT

2. Documentation update:
     Update "User's Guide"->"Data Types"->"Arrays" documentation
     create a new section: "User's Guide"->
                           "Functions and Operators"->
                           "Array Functions and Operators"


4. PL/pgSQL support for polymorphic types

5. SQL function support for polymorphic types.

6. Move as much of contrib/intarray into backend as makes sense,
    including migration to use polymorphic semantics (therefore make
    work on other than int where possible). Note: this may not happen
    for 7.4 as it looks to be fairly involved, at least at first glance.

7. Additional documentation and regression test updates

----------------------------------------------------------------------

Hopefully I haven't forgotten anything. If so, be gentle ;-)

Regards,

Joe