/usr/share/postgresql/10/extension/unit--1--2.sql is in postgresql-10-unit 5.0-1.
This file is owned by root:root, with mode 0o644.
The actual contents of the file can be viewed below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 | ALTER TYPE unit_accum_t
ADD ATTRIBUTE squares double precision;
CREATE OR REPLACE FUNCTION unit_accum(a unit_accum_t, u unit)
RETURNS unit_accum_t
AS $$SELECT (CASE WHEN a.s = '0'::unit THEN u ELSE a.s + u END, a.squares + value(u)^2, a.n + 1)::unit_accum_t$$
LANGUAGE SQL IMMUTABLE STRICT;
-- update v1 avg() aggregate
UPDATE pg_aggregate
SET agginitval = '(0,0,0)'
WHERE aggtransfn = 'unit_accum'::regproc AND agginitval = '(0,0)';
CREATE FUNCTION unit_var_pop(a unit_accum_t)
RETURNS double precision
AS $$SELECT CASE WHEN a.n > 0 THEN (a.squares - value(a.s)^2 / a.n) / a.n ELSE NULL END$$
LANGUAGE SQL IMMUTABLE STRICT;
CREATE AGGREGATE var_pop(unit)
(
sfunc = unit_accum,
stype = unit_accum_t,
finalfunc = unit_var_pop,
initcond = '(0,0,0)'
);
CREATE FUNCTION unit_var_samp(a unit_accum_t)
RETURNS double precision
AS $$SELECT CASE WHEN a.n > 1 THEN (a.squares - value(a.s)^2 / a.n) / (a.n - 1) WHEN a.n = 1 THEN 0 ELSE NULL END$$
LANGUAGE SQL IMMUTABLE STRICT;
CREATE AGGREGATE var_samp(unit)
(
sfunc = unit_accum,
stype = unit_accum_t,
finalfunc = unit_var_samp,
initcond = '(0,0,0)'
);
CREATE AGGREGATE variance(unit)
(
sfunc = unit_accum,
stype = unit_accum_t,
finalfunc = unit_var_samp,
initcond = '(0,0,0)'
);
CREATE FUNCTION unit_stddev_pop(a unit_accum_t)
RETURNS unit
AS $$SELECT CASE WHEN a.n > 0 THEN sqrt((a.squares - value(a.s)^2 / a.n) / a.n) * dimension(a.s) ELSE NULL END$$
LANGUAGE SQL IMMUTABLE STRICT;
CREATE AGGREGATE stddev_pop(unit)
(
sfunc = unit_accum,
stype = unit_accum_t,
finalfunc = unit_stddev_pop,
initcond = '(0,0,0)'
);
CREATE FUNCTION unit_stddev_samp(a unit_accum_t)
RETURNS unit
AS $$SELECT CASE WHEN a.n > 1 THEN sqrt((a.squares - value(a.s)^2 / a.n) / (a.n - 1)) * dimension(a.s) WHEN a.n = 1 THEN 0 * dimension(a.s) ELSE NULL END$$
LANGUAGE SQL IMMUTABLE STRICT;
CREATE AGGREGATE stddev_samp(unit)
(
sfunc = unit_accum,
stype = unit_accum_t,
finalfunc = unit_stddev_samp,
initcond = '(0,0,0)'
);
CREATE AGGREGATE stddev(unit)
(
sfunc = unit_accum,
stype = unit_accum_t,
finalfunc = unit_stddev_samp,
initcond = '(0,0,0)'
);
|