Thread: How do I create an array?
I'm trying to create a function that operates on integer[]. It works great if
I have an integer[] column already in the table. However I don't see how to
create an array dynamically given two integers.
I could paste together a string representation and then cast it back using
something like ('{'||a||','||b||'}')::integer[] but that seems terribly
wasteful. And it doesn't seem to work anyways.
There must be something like the point() function for arrays, no? I'm probably
missing something obvious or looking in the wrong place? I just don't see it.
--
greg
Greg Stark <gsstark@mit.edu> writes:
> There must be something like the point() function for arrays, no?
Not in general --- how would you declare the input and result types
of such a function? Unless you make a bespoke function for every
array-element datatype, there's no evident way to declare it in
Postgres' type system.
It would be useful to have something like that, but AFAICS it would
take inventing some specialized SQL syntax construct to do it.
Which no one has gotten around to defining, let alone implementing.
(Ideas welcome, though.)
IIRC, pltcl has some reasonably convenient facilities for creating
SQL arrays based on translation from Tcl arrays. Also, I'll bet
that Joe Conway's upcoming plr makes it just as easy as pie (once
you learn R, anyway). But plain SQL and plpgsql don't really have
much to fall back on to support such things.
regards, tom lane
Tom Lane wrote:
> Also, I'll bet that Joe Conway's upcoming plr makes it just as easy as pie
> (once you learn R, anyway). But plain SQL and plpgsql don't really have
> much to fall back on to support such things.
>
You mean something like this?
create or replace function vec(float, float) returns _float8 as 'c(arg1,arg2)'
language 'plr';
select vec(1.23, 1.32);
vec
-------------
{1.23,1.32}
(1 row)
Actually, while I was at it I also wrote a C function called "array" which can
be declared to take as many arguments (to the max allowed) and return a
corresponding array. It is useful since R likes to work with arrays. E.g:
CREATE OR REPLACE FUNCTION array (float8, float8) RETURNS float8[] AS
'$libdir/plr','array' LANGUAGE 'C' WITH (isstrict);
regression=# select array(1.24,2.35);
array
-------------
{1.24,2.35}
(1 row)
CREATE OR REPLACE FUNCTION array (float8, float8, float8) RETURNS float8[] AS
'$libdir/plr','array' LANGUAGE 'C' WITH (isstrict);
regression=# select array(1.24,2.35,4.57);
array
------------------
{1.24,2.35,4.57}
(1 row)
I'm still working out the kinks with PL/R, and I've just started the
documentation, but it's pretty much feature complete, at least as far as the
first release goes.
Per previous discussion it won't be usable for trigger functions, and it
doesn't handle greater than 2d arrays as arguments or return types. But it
does allow scalar, 1d and 2d array, and composite type arguments. And it can
return scalar, 1d and 2d arrays, and composite types (i.e. supports table
functions).
I've been developing against cvs, but I tested today against 7.3.2 and it
passed its regression test. If you're feeling adventurous, let me know and
I'll send a copy directly.
Joe
Joe Conway <mail@joeconway.com> writes:
> Actually, while I was at it I also wrote a C function called "array" which can
> be declared to take as many arguments (to the max allowed) and return a
> corresponding array. It is useful since R likes to work with arrays. E.g:
> 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.
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.
regards, tom lane
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
Joe Conway <mail@joeconway.com> writes:
> 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);
You could write that definition, but the implementation could be hard:
I don't think there's any way for the function to know what datatype
it's been passed.
>> This morning I was musing about overloading the CAST syntax to allow
>> array construction, along the lines of
>> [snip]
> From SQL99
> <array value expression> ::= <array value constructor>
> | <array concatenation>
> | <value expression primary>
Oh cool, I had not thought to look in SQL99. Clearly we should lift
their syntax.
> 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):
Whether you are or not, I'd be inclined to ignore that and instead use
UNION's algorithm for deriving the union type. Consider
ARRAY [ 1, 1.1, 1.4e10 ]
You don't want it to type that as integer[], IMHO.
regards, tom lane