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
