2011/2/16 Merlin Moncure <mmonc...@gmail.com> > On Tue, Feb 15, 2011 at 7:51 AM, Dmitriy Igrishin <dmit...@gmail.com> > wrote: > > > > > > 2011/2/15 Merlin Moncure <mmonc...@gmail.com> > >> > >> On Mon, Feb 14, 2011 at 8:37 AM, Dmitriy Igrishin <dmit...@gmail.com> > >> wrote: > >> > 2011/2/14 Merlin Moncure <mmonc...@gmail.com> > >> >> > >> >> On Mon, Feb 7, 2011 at 3:15 PM, Julia Jacobson > >> >> <julia.jacob...@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 > 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.