Re: how can I select into an array?

On Fri, Feb 06, 2004 at 12:08:16PM -0500, Tom Lane wrote:
> "Andy Kriger" <akriger(at)greaterthanone(dot)com> writes:
> > I would like to select strings from a table and return them as an array
>
> You can do that beginning in 7.4 with the ARRAY(sub-select) construct.
>
> regression=# select f1 from text_tbl;
> f1
> -------------------
> doh!
> hi de ho neighbor
> (2 rows)
>
> regression=# select array(select f1 from text_tbl);
> ?column?
> ----------------------------
> {doh!,"hi de ho neighbor"}
> (1 row)
>
> regression=#
>
> In prior versions you could probably fake it with a loop in a plpgsql
> function, but it'd be kinda awkward.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend

Joe Conway supplied a 7.3 version of function that
could help you do this. I have not tested it in
7.2, however. The details of this function for
getting the next array index for appending to the
array are written up in http://www.varlena.com/GeneralBits/24.html

CREATE OR REPLACE FUNCTION array_next(text[]) returns int AS '
DECLARE
arr alias for $1;
high int;
BEGIN
high := 1 +
replace(split_part(array_dims(arr),'':'',2),'']'','''')::int;
RETURN high;
END;
' LANGUAGE 'plpgsql' IMMUTABLE STRICT;

create table mytable (myarray text[]);
insert into mytable values ('{"abc","d e f"}');
update mytable set myarray[array_next(myarray)] = 'new element';
regression=# select * from mytable;
myarray
-----------------------------
{abc,"d e f","new element"}
(1 row)

Elein