pyagg.sql

pyagg.sql

DROP DATABASE testdb;
CREATE DATABASE testdb;

\c testdb

CREATE LANGUAGE plpython2u;

--
-- min()
--
CREATE FUNCTION float8_pymin(s float8, n float8)
  RETURNS float8
AS $$
    global s

    if n is not None:
        if s is None or n < s:
            s = n
    return s
$$ LANGUAGE plpython2u;

CREATE AGGREGATE pymin (float8)
(
    sfunc = float8_pymin,
    stype = float8
);

--
-- max()
--
CREATE FUNCTION float8_pymax(s float8, n float8)
  RETURNS float8
AS $$
    global s

    if n is not None:
        if s is None or n > s:
            s = n
    return s
$$ LANGUAGE plpython2u;

CREATE AGGREGATE pymax (float8)
(
    sfunc = float8_pymax,
    stype = float8
);

--
-- avg()
--
CREATE FUNCTION float8_pyavg(s float8[], n float8)
  RETURNS float8[]
AS $$
    global s

    if n is not None:
        if s is None:
            # init sum,count
            s = [0,0]
        s[0] = s[0] + n
        s[1] = s[1] + 1
    return s
$$ LANGUAGE plpython2u;


CREATE FUNCTION float8_pyavg_final(s float8[])
  RETURNS float8
AS $$
    global s

    if s is not None:
        return s[0]/s[1]
    return None
$$ LANGUAGE plpython2u;


CREATE AGGREGATE pyavg (float8)
(
    sfunc = float8_pyavg,
    stype = float8[],
    finalfunc = float8_pyavg_final
);

--
-- test
--
CREATE TABLE t1 (
  uid INTEGER PRIMARY KEY
);

INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6);

SELECT pymin(uid::float8),
       pymax(uid::float8),
       pyavg(uid::float8)
  FROM t1;