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

Re: arrays and functions in plpgsql

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Todd Kover <kovert(at)omniscient(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: arrays and functions in plpgsql
Date: 2004-09-17 23:16:14
Message-ID: 26099.1095462974@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
Todd Kover <kovert(at)omniscient(dot)com> writes:
> 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';

Array indexes start from one by default, so I think you need

        if v_state[2] != 0 THEN
                v_avg := v_state[1] / v_state[2];

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

This initcond will not work either, since that's not valid syntax for an
array (and we don't yet support nulls as array elements anyway).
But you are already testing for v_state IS NULL, so just leave out the
initcond and let it default to a NULL array.

>         IF v_state is NULL THEN   
>                 v_state[0] := 0;
>                 v_state[1] := 0;
>                 v_state[2] := NULL;

This is going to be a problem too.  You could write

		v_state := ''{0,0,0}'';

but initializing the array one-element-at-a-time won't work in 7.4.
(I think it will work in 8.0, FWIW.)  You'll have to invent some
convention other than NULL for the third entry, also.  Maybe use
a 4-element array and let the 4th element be 1 or 0 according to
whether the 3rd element is really meaningful?

			regards, tom lane

In response to

pgsql-novice by date

Next:From: Todd KoverDate: 2004-09-17 23:56:29
Subject: Re: arrays and functions in plpgsql
Previous:From: Todd KoverDate: 2004-09-17 23:06:57
Subject: arrays and functions in plpgsql

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