Обсуждение: plpgsql: type of array cells
I'm trying to use an array of objects in plpgsql (postgresql 8.4): drop type if exists test_t cascade; create type test_t AS ( i integer, s text ); create or replace function test2() RETURNS SETOF test_t AS $$ DECLARE arr test_t ARRAY[3]; tmp test_t; BEGIN FOR i in 1 .. 3 LOOP -- ok. Can write to test_t.i tmp.i:=i; -- ok. Can assign a cell from arr to a test_t object arr[i]:=tmp; -- error: arr[i].i=3; RETURN NEXT tmp; END LOOP; END; $$ LANGUAGE plpgsql; I'm getting an error when attempting to interpret this function: NOTICE: drop cascades to function test2() ERROR: syntax error at or near "." LINE 21: arr[i].i=3; ^ ********** Error ********** ERROR: syntax error at or near "." SQL state: 42601 Character: 272 Isn't arr[i] of type test_t?? Thanks, Amit
I'm trying to use an array of objects in plpgsql (postgresql 8.4): drop type if exists test_t cascade; create type test_t AS ( i integer, s text ); create or replace function test2() RETURNS SETOF test_t AS $$ DECLARE arr test_t ARRAY[3]; tmp test_t; BEGIN FOR i in 1 .. 3 LOOP -- ok. Can write to test_t.i tmp.i:=i; -- ok. Can assign a cell from arr to a test_t object arr[i]:=tmp; -- error: arr[i].i=3; RETURN NEXT tmp; END LOOP; END; $$ LANGUAGE plpgsql; I'm getting an error when attempting to interpret this function: NOTICE: drop cascades to function test2() ERROR: syntax error at or near "." LINE 21: arr[i].i=3; ^ ********** Error ********** ERROR: syntax error at or near "." SQL state: 42601 Character: 272 Isn't arr[i] of type test_t?? Thanks, Amit
On Wed, Oct 5, 2011 at 7:07 PM, Amit Dor-Shifer <amit.dor.shifer@gmail.com> wrote: > I'm trying to use an array of objects in plpgsql (postgresql 8.4): > > drop type if exists test_t cascade; > > create type test_t AS > ( > i integer, > s text > ); > > create or replace function test2() > RETURNS SETOF test_t AS > $$ > DECLARE > arr test_t ARRAY[3]; > tmp test_t; > BEGIN > FOR i in 1 .. 3 > LOOP > -- ok. Can write to test_t.i > tmp.i:=i; > -- ok. Can assign a cell from arr to a test_t object > arr[i]:=tmp; > -- error: > arr[i].i=3; > RETURN NEXT tmp; > END LOOP; > END; > $$ > LANGUAGE plpgsql; > > I'm getting an error when attempting to interpret this function: > > NOTICE: drop cascades to function test2() > ERROR: syntax error at or near "." > LINE 21: arr[i].i=3; > ^ > > ********** Error ********** > > ERROR: syntax error at or near "." > SQL state: 42601 > Character: 272 > > Isn't arr[i] of type test_t?? It is, but it looks like you either have to break it up like this: tmp := arr[i]; tmp.i = 3; arr[i] := tmp; or do it like this: arr[i] := row(3, arr[i].s); since the second form is allowed, I guess what you're trying should probably work too. generally though, the best way to do arrays is to avoid iteration as much as possible. merlin