> > May I ask some more complex? I want to use ONE multidimensial array -
> > the "id", "bytes_in" and "bytes_out". By another words, I need an
> > array, each element of which must contain 3 values: ttc_id, bytes_in,
> > bytes_out.
> >
> > I think it can be done like this:
>
> It's problem. You have to wait for 8.3 where composite types in arrays
> are supported, or simply use three array variables (or use plperl or
> plpython). Arrays in plpgsql can be slow, if you often update big
> arrays.
Ok, got it, thanks for explanations. Actually I already use that.
If someone interesting here is the example.
initialisation:
FOR p_tmp IN SELECT DISTINCT ON(ttc_id) ttc_id FROM ttc_ids
LOOP
-- get next value for index
i = array_upper(p_ttc_ids, 1) + 1; IF i IS NULL THEN i := 0; END IF;
--RAISE NOTICE '[%]', i;
p_ttc_ids[i] := p_tmp.ttc_id;
p_bytes_in[i] := 0;
p_bytes_out[i] := 0;
END LOOP;
example work:
X_ttc_id := ...
FOR i IN COALESCE(array_lower(p_ttc_ids,1),1) ..
COALESCE(array_upper(p_ttc_ids,1),-1)
LOOP
IF p_ttc_ids[i] = X_ttc_id THEN
p_bytes_in[i] := p_bytes_in[i] + X_bytes_in;
p_bytes_out[i] := p_bytes_out[i] + X_bytes_out;
END IF;
END LOOP;
It looks ugly but exactly for one of my functions (about 30...40
ttc_id's; function do very small computations like above; but started
for many rows) it is about 25% faster than use temporary table.
--
engineer