2011/2/15 Merlin Moncure <mmoncure@xxxxxxxxx>
That's debatable, but putting that aside it's still good to learn theOn Mon, Feb 14, 2011 at 8:37 AM, Dmitriy Igrishin <dmitigr@xxxxxxxxx> wrote:
> 2011/2/14 Merlin Moncure <mmoncure@xxxxxxxxx>
>>
>> On Mon, Feb 7, 2011 at 3:15 PM, Julia Jacobson <julia.jacobson@xxxxxxxx>
>> 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
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
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.
