Re: How do I create an array? - Mailing list pgsql-general
From | Joe Conway |
---|---|
Subject | Re: How do I create an array? |
Date | |
Msg-id | 3E428B62.4030409@joeconway.com Whole thread Raw |
In response to | Re: How do I create an array? (Tom Lane <tgl@sss.pgh.pa.us>) |
Responses |
Re: How do I create an array?
(Tom Lane <tgl@sss.pgh.pa.us>)
|
List | pgsql-general |
Tom Lane wrote: > Joe Conway <mail@joeconway.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
pgsql-general by date: