Re: [SQL] Error when using array variable

[Gnanavel S]

On 7/22/05, Dongsoo Yoon <[EMAIL PROTECTED]> wrote:

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$

 

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.

      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.