|
| 1 | +-- initialization SQL script for astroplpython |
| 2 | +-- functions |
| 3 | + |
| 4 | +-- we use python 3 |
| 5 | +create LANGUAGE plpython3u; |
| 6 | + |
| 7 | +-- create measurement p(f) |
| 8 | +create type p_f as (power float8, frequency float8); |
| 9 | + |
| 10 | +-- create measurement x(t) |
| 11 | +create type x_t as (x float8, time float8); |
| 12 | + |
| 13 | +-- next two functions support creation of an |
| 14 | +-- aggregate function to gather X(t) measurements |
| 15 | +-- from a database table. |
| 16 | +create function x_t_accum (t1 x_t[], t2 x_t) |
| 17 | +returns x_t[] as |
| 18 | +$$ |
| 19 | + select array_append($1, $2)::x_t[]; |
| 20 | +$$ language SQL immutable; |
| 21 | + |
| 22 | +CREATE AGGREGATE x_t_accum (x_t) |
| 23 | +( |
| 24 | + sfunc = array_append, |
| 25 | + stype = x_t[], |
| 26 | + initcond = '{}' |
| 27 | +); |
| 28 | + |
| 29 | +-- the core "final function" which actually does the |
| 30 | +-- calculation of the periodogram |
| 31 | +create or replace FUNCTION calc_lsp (data x_t[]) |
| 32 | + RETURNS setof p_f |
| 33 | +AS $$ |
| 34 | + |
| 35 | + from astroplpython.data.Timeseries import x_t |
| 36 | + from astroplpython.proc.LSPeriodogram import LSPeriodogram |
| 37 | + |
| 38 | + def strToXTArray (strarr): |
| 39 | + x_t_list = [] |
| 40 | + for v in strarr: |
| 41 | + v = v.replace("(","") |
| 42 | + v = v.replace(")","") |
| 43 | + vals = v.split(",") |
| 44 | + x = vals[0] |
| 45 | + t = vals[1] |
| 46 | + x_t_list.append(x_t(x,t)) |
| 47 | + return x_t_list |
| 48 | + |
| 49 | + lsp = LSPeriodogram(strToXTArray(data)) |
| 50 | + pgram = lsp.pgram() |
| 51 | + |
| 52 | + return pgram |
| 53 | + |
| 54 | +$$ LANGUAGE plpython3u IMMUTABLE; |
| 55 | + |
| 56 | +-- provides changing setof p_f into p_f[] |
| 57 | +-- which is more convenient for bulk runs which |
| 58 | +-- insert results from each run on a row in a |
| 59 | +-- results table |
| 60 | +create function calc_lsp_arr_p_f(in x_t[]) |
| 61 | + returns p_f[] |
| 62 | +AS $$ |
| 63 | + select array ( select calc_lsp($1) ); |
| 64 | +$$ language sql immutable; |
| 65 | + |
| 66 | +-- And finally, the actual function for calculation of LSPeriodogram. |
| 67 | +-- in principle, we want an aggregate function. |
| 68 | +-- but this is a bad idea, the finalfunc can only take |
| 69 | +-- a single argument, which is of stype |
| 70 | +-- OTOH, we might use SD, GD arrays to provide |
| 71 | +-- things like f_high, f_over parameters to functions |
| 72 | +-- |
| 73 | +-- see http://www.postgresql.org/docs/9.2/interactive/plpython-sharing.html |
| 74 | +-- |
| 75 | +-- which is workable, but 'feels' wrong. Certainly a |
| 76 | +-- violation of the 'functional' approach we want to take |
| 77 | +CREATE AGGREGATE lsp (x_t) |
| 78 | +( |
| 79 | + sfunc = x_t_accum, |
| 80 | + stype = x_t[], |
| 81 | + finalfunc = calc_lsp_arr_p_f, |
| 82 | + initcond = '{}' |
| 83 | +); |
| 84 | + |
| 85 | + |
0 commit comments