Re: SELECT INTO array[i] with PL/pgSQL - Mailing list pgsql-general

On Tue, Feb 15, 2011 at 7:51 AM, Dmitriy Igrishin <dmitigr@gmail.com> wrote:
>
>
> 2011/2/15 Merlin Moncure <mmoncure@gmail.com>
>>
>> On Mon, Feb 14, 2011 at 8:37 AM, Dmitriy Igrishin <dmitigr@gmail.com>
>> wrote:
>> > 2011/2/14 Merlin Moncure <mmoncure@gmail.com>
>> >>
>> >> On Mon, Feb 7, 2011 at 3:15 PM, Julia Jacobson
>> >> <julia.jacobson@arcor.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
>
> Please note, that OP wants array aggregate of column of table rather
> than array aggregate of composite type. So, in case of OP array_agg()
> is much cleaner and its not debatable:
>
> select into examples array_agg(value) from example;
> VS
> select array(select e from example e where row_id in (1,2,3)) into examples

er, you are not comparing apples to apples:

select into examples array_agg(value) from example;
VS

select into examples array(select value from example);

Yeah, array_agg is kinda sorta easier, but the point I was making is
that array() can be employed against a much broader array of problems,
not just when using composite types.

for example,
select f.*, array(select value from bar where foo_id = f.foo_id) as
values from foo;
vs
select f.*, array_agg(value) as values from foo group by foo.a, foo.b etc

Are completely different queries, and have non-trivial plan interactions.

merlin