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

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

> On Tue, Feb 15, 2011 at 7:51 AM, Dmitriy Igrishin <dmitigr(at)gmail(dot)com>
> wrote:
> >
> >
> > 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
>
> 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
>
Huh? I don't clearly understand where here "comparison of apples to apples"
?

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

--
// Dmitriy.