Re: how to use array with "holes" ?

hello,

you have to initialise array before using. Like:

declare a int[] = '{0,0,0,0,0, .................}';
begin
 a[10] := 11;

..

reason: older postgresql versions unsuported nulls in array

regards
Pavel

2007/5/31, Anton <anton200@gmail.com>:
> Hi.
>
> I want to use array for store some values (bytes_in and bytes_out) and
> use array index as "id". But I got an errors...
> My example function extract traf_id, bytes_in, bytes_out and try to
> fill an array, like this
>
> CREATE OR REPLACE FUNCTION myf_test() RETURNS void
> AS $$
> DECLARE
>  p_tmp RECORD;
>  p_last_cpnt RECORD;
>  p_bytes_in bigint[];
>  p_bytes_out bigint[];
>  i int;
> BEGIN
>  SELECT * INTO p_last_cpnt FROM nn_cpnt WHERE account_id = 5 ORDER BY
> date_time DESC, cpnt_id DESC LIMIT 1;
>  IF FOUND THEN
>   FOR p_tmp IN SELECT ttc_id, bytes_in, bytes_out FROM nn_cpnt_traf
> WHERE cpnt_id = p_last_cpnt.cpnt_id ORDER BY ttc_id LOOP
>    --RAISE NOTICE '[%] -> [%] [%]', p_tmp.ttc_id, p_tmp.bytes_in,
> p_tmp.bytes_out;
>    i := p_tmp.ttc_id;
>    RAISE NOTICE 'i = [%]', i;
>    p_bytes_in[i] := p_tmp.bytes_in;
>    p_bytes_out[i] := p_tmp.bytes_out;
>    RAISE NOTICE 'p_bytes_in[%] = [%] / p_bytes_out[%] = [%]', i,
> p_bytes_in[i], i, p_bytes_out[i];
>   END LOOP;
>  END IF;
>  -- ... some work. And I prepare to "INSERT INTO tbl" from my array.
> So I iterate through my array (but now I just RAISE NOTICE here).
>  FOR i IN COALESCE(array_lower(p_bytes_in,1),1) ..
> COALESCE(array_upper(p_bytes_in,1),-1) LOOP
>    RAISE NOTICE 'p_bytes_in[%] = [%] / p_bytes_out[%] = [%]', i,
> p_bytes_in[i], i, p_bytes_out[i];
>  END LOOP;
> END;
> $$
> LANGUAGE plpgsql;
>
> But
> =# SELECT myf_test();
> NOTICE:  i = [1]
> NOTICE:  p_bytes_in[1] = [1907262814] / p_bytes_out[1] = [9308877139]
> NOTICE:  i = [5]
> ERROR:  invalid array subscripts
> CONTEXT:  PL/pgSQL function "myf_test" line 14 at assignment
>
> There are "hole" between 1 and 5, so I think that is the problem... I
> try to use temporary table (truncate it always before computations),
> but it seems slowly than arrays. I think arrays are less complicated
> so they operate faster.
> Please, help. Explain me how to use array?
> --
> engineer
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>