Re: SELECT INTO array[i] with PL/pgSQL

On Mon, Feb 14, 2011 at 8:37 AM, Dmitriy Igrishin <dmitigr(at)gmail(dot)com> wrote:
> 2011/2/14 Merlin Moncure <mmoncure(at)gmail(dot)com>
>>
>> On Mon, Feb 7, 2011 at 3:15 PM, Julia Jacobson <julia(dot)jacobson(at)arcor(dot)de>
>> wrote:
>> > Dear PostgreSQL community,
>> >
>> > Please consider the following minimal example:
>> >
>> > CREATE TABLE example (row_id SERIAL, value TEXT);
>> > INSERT INTO example(value) VALUES ('val1');
>> > INSERT INTO example(value) VALUES ('val2');
>> > INSERT INTO example(value) VALUES ('val3');
>> >
>> > CREATE OR REPLACE FUNCTION foo()
>> > RETURNS TEXT
>> > AS
>> > $$
>> > DECLARE
>> >  a TEXT;
>> >  b TEXT[];
>> >  i INT;
>> > BEGIN
>> >  FOR i in 1..3 LOOP
>> >    SELECT INTO a    value FROM example WHERE row_id=i;  -- This works
>> >    b[i] := a;                                           -- perfectly!
>> > --  SELECT INTO b[i] value FROM example WHERE row_id=i;  Doesn't work!
>> >  END LOOP;
>> >    RETURN b[2];
>> > END;
>> > $$
>> > LANGUAGE 'plpgsql';
>> >
>> > The error message indicates a problem with selecting values into an
>> > array.
>> > I have read the documentation carefully and have done extensive web
>> > search,
>> > but a more verbose error message and some additional explanation would
>> > help
>> > me to understand the problem.
>> > Is there a way to select values directly into an array without the
>> > assignment from an additional variable?
>>
>> You got some good answers downthread but the key with arrays in
>> pl/pgsql is to avoid iterative processing whenever possible,
>> *especially* when building the array.  The key is to convert the loop
>> to a query, and wrap the query with the array() syntax construction.
>> For example, your construction above could be written like this:
>>
>> select array(select value from example where row_id in (1,2,3)) into b;
>>
>> you can also use row types:
>> DECLARE
>>  examples example[];
>> BEGIN
>>  select array(select e from example e where row_id in (1,2,3)) into
>> examples;
>>
>> Using array(...) or array_agg() vs building with assignment or
>> array_cat() will be MUCH faster.
>
> array_agg() is more readable and clear :-P

That's debatable, but putting that aside it's still good to learn the
ins and outs of array() array_agg aggregates, and array() is syntax
that converts set returning one column subquery into an array. They
are NOT the same thing, and when nesting it's trivial to stack layers
with array() that is difficult/impossible with array_agg().

merlin