Re: SQL99 ARRAY support proposal

Tom Lane wrote:
> Joe Conway <mail@joeconway.com> writes:
>>select ARRAY[1,2,3];
>>   result '{1,2,3}'
> 
> The array type is determined how?  I'd like this syntax better if there
> were a way to force the choice of array type...

What about:
select integer ARRAY[1,2,3];   result '{1,2,3}'::integer[]

>>select ARRAY[(select oid from pg_class order by relname)];
>>   result is array of all the oid's in pg_class in relname order
> 
> Puh-leez tell me that's not in the spec.  How is one supposed to
> distinguish this usage from the scalar-subselect case?

Well, SQL99 has this:
<array value constructor> ::=  <array value list constructor>
<array value list constructor> ::=  ARRAY <left bracket or trigraph>        <array element list>        <right bracket
ortrigraph>
 

but SQL200x has this:

<array value constructor> ::=  <array value constructor by enumeration> |  <array value constructor by query>
<array value constructor by enumeration> ::=  ARRAY <left bracket or trigraph>        <array element list>
<rightbracket or trigraph>
 
<array value constructor by query> ::=  ARRAY <left paren>        <query expression> [ <order by clause> ]
<rightparen>
 


>>select ARRAY[1,2] || 3
>>   result '{1,2,3}'
> 
> 
> Datatypes?

maybe?

select integer ARRAY[1,2] || 3   result '{1,2,3}'::integer[]

> How many variants of the || operator do you plan to offer?

One for each builtin datatype[]/datatype pair (e.g. integer[]/integer), 
and another for each datatype[] (e.g. integer[]/integer[])

> What will be the side-effects on the parser's ability to pick one?

Not really sure. I figured I'd cross that bridge when I got to it. Are 
there specific landmines you're thinking of in there?
>>select ARRAY[1,2] || ARRAY[3,4]>>   result '{1,2,3,4}'

select integer ARRAY[1,2] || integer ARRAY[3,4]   result '{1,2,3,4}'::integer[]

Or else the use UNION's algorithm for deriving the element type (you 
suggested this previously, but I may have misunderstood)

Joe