Thread: Array manipulation/syntax question

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?

Thanks in advance.

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.