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(at)fetter(dot)org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!