Re: 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!