Thread: Error when using array variable
I use a function using array variable as following.
The function returns array type.
When array variable is allocated with any value,
allocating is not done.
allocating is not done.
I can't find why it is.
-------------------------------------------------------
CREATE OR REPLACE FUNCTION arr_test()
RETURNS NUMERIC[] AS $BODY$
DECLARE
v_count NUMERIC default 0;
v_dayIndex NUMERIC default 0;
t_modifiedTimes NUMERIC[];
v_testval NUMERIC default 0;
....
BEGIN
....
for v_count in 1..5 loop
v_dayIndex := v_dayIndex + 1;
t_modifiedTimes[v_dayIndex ] := v_count;
v_testval := t_modifiedTimes[v_dayIndex ] ; -------------->배열에 저장된 값 임시저장
raise exception '임의에러생성 t_modifiedTimes[v_dayIndex ]=', v_testval ;
-------------->강제적으로 exception 발생시킴
end loop;
....
return t_modifiedTimes;
END;
$BODY$ LANGUAGE plpgsql;
$BODY$ LANGUAGE plpgsql;
On Fri, Jul 22, 2005 at 06:24:03PM +0900, Dongsoo Yoon wrote: > > When array variable is allocated with any value, > allocating is not done. It's not clear what this means -- what are you expecting to happen, and what actually is happening? > v_testval := t_modifiedTimes[v_dayIndex ] ; -------------->?????? ?????? ?? ???????? > > raise exception '???????????? t_modifiedTimes[v_dayIndex ]=', v_testval ; Is this RAISE just for debugging purposes? Are you aware that you need to include a % character in the format string if you want to display the subsequent argument's value? http://www.postgresql.org/docs/8.0/static/plpgsql-errors-and-messages.html -- Michael Fuhr http://www.fuhr.org/~mfuhr/
On 7/22/05, Dongsoo Yoon <dsyoon@metasoftworks.com> wrote:
You need to initialse the array, Otherwise any value added to the array will be null.
I use a function using array variable as following.The function returns array type.When array variable is allocated with any value,
allocating is not done.I can't find why it is.-------------------------------------------------------CREATE OR REPLACE FUNCTION arr_test()RETURNS NUMERIC[] AS $BODY$DECLAREv_count NUMERIC default 0;v_dayIndex NUMERIC default 0;t_modifiedTimes NUMERIC[];v_testval NUMERIC default 0;....BEGIN....for v_count in 1..5 loopv_dayIndex := v_dayIndex + 1;
t_modifiedTimes[v_dayIndex ] := v_count;
You need to initialse the array, Otherwise any value added to the array will be null.
v_testval := t_modifiedTimes[v_dayIndex ] ; -------------->배열에 저장된 값 임시저장raise exception '임의에러생성 t_modifiedTimes[v_dayIndex ]=', v_testval ;-------------->강제적으로 exception 발생시킴end loop;....return t_modifiedTimes;END;
$BODY$ LANGUAGE plpgsql;
--
with regards,
S.Gnanavel
Satyam Computer Services Ltd.
On Fri, Jul 22, 2005 at 03:07:04PM +0530, Gnanavel S wrote: > On 7/22/05, Dongsoo Yoon <dsyoon@metasoftworks.com> wrote: > > CREATE OR REPLACE FUNCTION arr_test() > > RETURNS NUMERIC[] AS $BODY$ > > DECLARE > > v_count NUMERIC default 0; > > v_dayIndex NUMERIC default 0; > > t_modifiedTimes NUMERIC[]; > > v_testval NUMERIC default 0; > > .... > > BEGIN > > .... > > for v_count in 1..5 loop > > v_dayIndex := v_dayIndex + 1; > > > t_modifiedTimes[v_dayIndex ] := v_count; > > You need to initialse the array, Otherwise any value added to the array will > be null. This isn't necessary in 8.0, which we can infer is being used because of the dollar quotes. See the 8.0 Release Notes: http://www.postgresql.org/docs/8.0/static/release-8-0.html "Updating an element or slice of a NULL array value now produces a non-NULL array result, namely an array containing just the assigned-to positions." I don't recall if early 8.0 versions had any bugs in this respect, but it does indeed work in 8.0.3. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Michael Fuhr <mike@fuhr.org> writes:
> On Fri, Jul 22, 2005 at 03:07:04PM +0530, Gnanavel S wrote:
>> You need to initialse the array, Otherwise any value added to the array will
>> be null.
> This isn't necessary in 8.0, which we can infer is being used because
> of the dollar quotes.
However, plpgsql wasn't fixed to follow that behavior till 8.0.2 or so.
regards, tom lane