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

2011/2/15 Merlin Moncure <mmoncure(at)gmail(dot)com>

> 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
>
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

--
// Dmitriy.