Array manipulation/syntax question

On Mon, May 09, 2005 at 07:05:18PM -0400, Bart Grantham wrote:

Hello again. I had a problem a few weeks ago with using IN ( some_array
) having really rough performance. Special thanks to Ron Mayer for the
suggestion of using int_array_enum(some_array) to join against. I had
to upgrade to PG8 but that technique works really well. Now I have a
question about array manipulation. Specifically, how can I build an
array from the result of a query without resorting to array_append()? I
have a recursive function that passes itself an array each time and I
want to build this array by taking a column out of a query (which is a
join from the input array and a table... see? recursive!). This
becomes a performance issue since the query tha wraps array_append could
be returning many thousands of rows. Hmm, my question is sounding more
complicated it should. Let me put it this way... how do I do this:

-- my_array is an INT[]

_my_array := select some_column from some_table;

... in plpgsql?

DECLARE
foo INTEGER[];
BEGIN
SELECT INTO foo ARRAY(SELECT some_int_col FROM some_tab);
/* Your Stuff Goes Here */
END;

HTH :)

Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!

On Mon, May 09, 2005 at 07:05:18PM -0400, Bart Grantham wrote:

Let me put it this way... how do I do this:

-- my_array is an INT[]

_my_array := select some_column from some_table;

In PostgreSQL 7.4 and later you can use an array constructor:

my_array := ARRAY(SELECT some_column FROM some_table);

See "Array Constructors" in the "Value Expressions" section of the
"SQL Syntax" chapter of the documentation:

http://www.postgresql.org/docs/8.0/interactive/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Bart Grantham wrote:

Hello again. I had a problem a few weeks ago with using IN ( some_array
) having really rough performance. Special thanks to Ron Mayer for the
suggestion of using int_array_enum(some_array) to join against. I had
to upgrade to PG8 but that technique works really well. Now I have a
question about array manipulation. Specifically, how can I build an
array from the result of a query without resorting to array_append()?

Well, the same contrib module with int_array_enum() also has
aggregate called int_array_aggregate that turns a column into an array.

-- my_array is an INT[]

_my_array := select some_column from some_table;

so this:
SELECT int_array_aggregate(some_column) from some_table;
might be what you want.

... in plpgsql?

Well... the function from the contrib module was in C, so I guess
it doesn't strictly meet what you asked, but it works very well
for me. ;-)

Ron Mayer

PS: the stuff in /contrib/intarray may be useful too, for
indexing, merging, and many other tricks on the integer arrays.