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

[Dmitriy Igrishin <dmitigr@xxxxxxxxx>]

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

merlin

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

-- 
// Dmitriy.