Обсуждение: 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