Skip site navigation (1) Skip section navigation (2)

arrays and functions in plpgsql

From: Todd Kover <kovert(at)omniscient(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: arrays and functions in plpgsql
Date: 2004-09-17 23:06:57
Message-ID: 200409172306.i8HN6v8t019739@guinness.omniscient.com (view raw or flat)
Thread:
Lists: pgsql-novice
I'm trying to write my own aggregate function.  I'm getting messed up
in both the sfunc and finalfunc and I'm reasonably certain it's because
I'm not understanding how to pass arrays in properly.  I didn't piece it
together from the docs and I didn't find examples of how to do this.

I'm running postgresql 7.4.3.

I have:

create or replace function float8_jitter_sum(float8[])
        returns float8 as '
declare
        v_state ALIAS FOR $1;
        v_avg float8;
BEGIN
        v_avg := NULL;
        if v_state[1] != 0 THEN
                v_avg := v_state[0] / v_state[1];
        END IF;
        return v_avg;
END;
' language 'plpgsql';

as an eventual final function.  When I call it by hand via:

select float8_jitter_sum('{5,6,.3}');
select float8_jitter_sum('{5,9}');
 
I get back nothing where I think I should get back division of the first
two elements of the array.

What am I missing?

If you're particularly curious, I've attached the entirity of what
my aggregate code looks like with an example table and select.  (I'm
basically adding up the variance between an ordered list of elements,
skipping things where the previous element is NULL).  Since I havne't
gotten past the above, I haven't really looked closely into what else
I'm doing wrong (I'm certain I am because the final select gives me
an array value error), but if you spot something obvious, that'd be
appreciated to.

thanks in advance,
-Todd

----<snip>----
create or replace function float8_jitter_add(float8[], interval)
        returns float8[3] as '
declare
        v_old_state ALIAS FOR $1;
        v_rtt ALIAS FOR $2;
        v_state[3] float8;   
BEGIN
        IF v_state is NULL THEN   
                v_state[0] := 0;
                v_state[1] := 0;
                v_state[2] := NULL;
        ELSIF v_rtt IS NOT NULL THEN 
                if v_state[2] IS NOT NULL THEN
                        v_state[0] := v_old_state[0] + (v_old_state[2] - v_rtt);  
                        v_state[1] := v_old_state[1] + 1;
                END IF;
                v_state[2] := v_tt;
        ELSE
                v_state[2] := NULL;
        END IF;
        return v_state;
END;
' language 'plpgsql';

create or replace function float8_jitter_sum(float8[])
        returns float8 as '
declare
        v_state ALIAS FOR $1;
        v_avg float8;
BEGIN
        v_avg := NULL;
        if v_state[1] != 0 THEN
                v_avg := v_state[0] / v_state[1];
        END IF;
        return v_avg;
END;
' language 'plpgsql';

drop aggregate jitter( interval );
create aggregate jitter (
        basetype = interval,
        sfunc = float8_jitter_add,
        stype = float8[],
        finalfunc = float8_jitter_sum,
        initcond = '(NULL,NULL)'   
);


create table test (
        thing   integer,
        start   timestamp,
        finish  timestamp 
);

insert into test values (1, current_timestamp, current_timestamp + '1 sec');
insert into test values (1, current_timestamp, current_timestamp + '2 sec');

select thing, jitter(finish-start) from test group by thing;


Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2004-09-17 23:16:14
Subject: Re: arrays and functions in plpgsql
Previous:From: Kumar SDate: 2004-09-17 18:59:00
Subject: ERROR: COPY <Table Name> FROM <file>

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group