Re: How do I create an array?

Tom Lane wrote:
> Joe Conway <mail(at)joeconway(dot)com> writes:
>>CREATE OR REPLACE FUNCTION array (float8, float8) RETURNS float8[] AS
>>'$libdir/plr','array' LANGUAGE 'C' WITH (isstrict);
>
> Yeah, that's what I was referring to by a "bespoke function". You'd
> need one for every datatype; plus an entry in pg_proc for every number
> of arguments you want to support (and it won't scale past MAX_FUNC_ARGS).
> Doesn't seem like the avenue to a general solution.

Agreed. That's why I never sent it in to patches. Of course, I also wrote:

CREATE OR REPLACE FUNCTION array_push (_float8, float8)
RETURNS float8[]
AS '$libdir/plr','array_push'
LANGUAGE 'C';

Still not a general solution because of the need-one-for-each-datatype issue,
but it at least allows plpgsql to build an array, e.g.:

create or replace function array_accum(_float8, float8) returns float8[] as '
DECLARE
inputarr alias for $1;
inputval alias for $2;
BEGIN
if inputarr is null then
return array(inputval);
else
return array_push(inputarr,inputval);
end if;
END;
' language 'plpgsql';

BTW, while playing with this I noted that creating the function like:
create or replace function array_accum(float8[], float8)
didn't seem to work. Is that a known issue? I also noticed you fixed a similar
issue in that last day or two, so maybe its no longer a problem. (checks --
yup, looks like it's fixed now).

It seems like you should be able to define the function:
CREATE OR REPLACE FUNCTION array (any)
RETURNS anyarray
AS '$libdir/plr','array'
LANGUAGE 'C' WITH (isstrict);

since return value carries along its own element type.

> This morning I was musing about overloading the CAST syntax to allow
> array construction, along the lines of
>
> CAST((x,y,z+2) AS float8[])
>
> Perhaps multidimensional arrays could be done like this
>
> CAST(((a11,a12,a13), (a21,a22,a23)) AS float8[])
>
> But there are other ways you could imagine doing it, too.

From SQL99
<array value expression> ::= <array value constructor>
| <array concatenation>
| <value expression primary>

<array concatenation> ::= <array value expression 1>
<concatenation operator>
<array value expression 2>
<array value expression 1> ::= <array value expression>
<array value expression 2> ::= <array value expression>

<array value constructor> ::= <array value list constructor>
<array value list constructor> ::= ARRAY <left bracket or trigraph>
<array element list>
<right bracket or trigraph>
<array element list> ::= <array element> [ { <comma> <array element> }... ]
<array element> ::= <value expression>

So if I read that correctly, we'd want:

ARRAY [x, y, z+2]

and in section 6.4 SQL99 indicates that the array datatype should be derived
from the datatype of its first element (again, not sure I'm reading the spec
correctly):

6.4 <contextually typed value specification>
1) The declared type DT of an <empty specification> ES is ET ARRAY[0], where
the element type ET is determined by the context in which ES appears. ES is
effectively replaced by CAST ( ES AS DT ).

Does that make sense?

Joe