From: | Anton <anton200(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | how to use array with "holes" ? |
Date: | 2007-05-31 18:49:28 |
Message-ID: | 8cac8dd0705311149x72d901acyf49bf329d7966ec1@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Vincenzo Romano | 2007-05-31 19:25:27 | Re: Numeric performances |
Previous Message | Teodor Sigaev | 2007-05-31 18:27:06 | Re: TSEARCH2: disable stemming in indexes and triggers |