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

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

>
> merlin
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
// Dmitriy.