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

Hey,

2011/2/8 Edoardo Panfili <edoardo(at)aspix(dot)it>

> On 07/02/11 22.15, Julia Jacobson 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';
>>
>
> this one seems work...
>
>
> CREATE OR REPLACE FUNCTION foo()
> RETURNS TEXT
> AS
> $$
> DECLARE
> b TEXT[];
> i INT;
> BEGIN
> FOR i in 1..3 LOOP
> b[i]:= value FROM example WHERE row_id=i;
> END LOOP;
> RETURN b[2];
> END;
> $$
> LANGUAGE 'plpgsql';
>
>
> Edoardo
>
>
CREATE OR REPLACE FUNCTION public.f1()
RETURNS void
LANGUAGE plpgsql
STRICT
AS $function$
DECLARE
a_ text[];
t_ text; -- just for example of usage
BEGIN
SELECT INTO a_ array_agg(dat) FROM t1;

-- usage example:
FOR t_ IN SELECT unnest(a_) LOOP
RAISE NOTICE '%', t_;
END LOOP;
END;
$function$

create table t1 (id serial, dat text);
insert into t1 (dat) select 'dima';
insert into t1 (dat) select 'alex';
insert into t1 (dat) select 'vasya';

dmitigr=> select f1();
NOTICE: dima
NOTICE: alex
NOTICE: vasya

--
// Dmitriy.