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
