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

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.

merlin