From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Amit Dor-Shifer <amit(dot)dor(dot)shifer(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: plpgsql: type of array cells |
Date: | 2011-10-06 16:09:34 |
Message-ID: | CAHyXU0xARXJiJv-RadTY1kZ_OK6dzJbeOAyShWHuOS8PJ0sB1Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, Oct 5, 2011 at 7:07 PM, Amit Dor-Shifer
<amit(dot)dor(dot)shifer(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Adam Cornett | 2011-10-06 16:20:23 | Re: Backup Database Question |
Previous Message | Andrew Sullivan | 2011-10-06 16:09:30 | Re: Never-idle autovacuum, and does (auto)vacuuming fill the OS cache ? |