On Mon, Feb 7, 2011 at 3:15 PM, Julia Jacobson <julia.jacobson@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.
merlin