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

[Dmitriy Igrishin <dmitigr@xxxxxxxxx>]

Hey,

2011/2/8 Edoardo Panfili <edoardo@xxxxxxxx>

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.