-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathastroplpython.sql
More file actions
199 lines (167 loc) · 5.39 KB
/
Copy pathastroplpython.sql
File metadata and controls
199 lines (167 loc) · 5.39 KB
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
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
-- initialization SQL script for astroplpython
-- functions
-- we use python 3
create LANGUAGE plpython3u;
-- create measurement type P(f)
create type p_f as (power float8, frequency float8);
-- create measurement type X(t)
create type x_t as (value float8, time float8);
-- next two functions support creation of an
-- aggregate function to gather X(t) measurements
-- from a database table.
-- This function is for accumulation after first
-- measurement is gathered and x_t[] is started
create or replace function x_t_accum (t1 x_t[], t2 x_t)
returns x_t[] as
$$
select array_append($1, $2)::x_t[];
$$ language SQL immutable;
-- this gets things started for accumulation of x_t[]
CREATE AGGREGATE x_t_accum (x_t)
(
sfunc = array_append,
stype = x_t[],
initcond = '{}'
);
create or replace function p_f_accum (t1 p_f[], t2 p_f)
returns p_f[] as
$$
select array_append($1, $2)::p_f[];
$$ language SQL immutable;
-- this gets things started for accumulation of p_f[]
CREATE AGGREGATE p_f_accum (p_f)
(
sfunc = array_append,
stype = p_f[],
initcond = '{}'
);
-- the core "final function" which actually does the
-- calculation of the periodogram
create or replace FUNCTION calc_lsp (data x_t[], f_low numeric, f_high numeric, f_bins integer)
RETURNS setof p_f
AS $$
from astroplpython.data.TimeMeasurement import x_t
from astroplpython.function.signal.LSPeriodogram import LSPeriodogram
from astroplpython.exception.ListException import EmptyListException
# debugging?
import logging
import sys
logging.basicConfig(stream=sys.stderr)
log = logging.getLogger( "astroplpython.function.signal")
log.setLevel(logging.DEBUG)
# calculate based on passed parameters
pgram = []
try:
pgram = LSPeriodogram.calculate(x_t.dbStrToArray(data), f_low, f_high, f_bins)
except EmptyListException as el:
log.warn ("calc_lsp was passed an empty list, ignoring");
except ZeroDivisionError as ze:
log.warn ("calc_lsp got division by zero, ignoring");
except ValueError as ve:
log.warn ("calc_lsp got ValueError, ignoring");
return pgram
$$ LANGUAGE plpython3u IMMUTABLE;
-- Find the maximum power in a list of p_f[]
--
create or replace FUNCTION max_power (data p_f[])
RETURNS setof p_f
AS $$
from astroplpython.data.PowerFrequencyMeasurement import p_f
from astroplpython.function.statistic.Maximum import Maximum
import logging
import sys
logging.basicConfig(stream=sys.stderr)
log = logging.getLogger( "astroplpython.function.statistic")
p_f_list = []
# calculate based on passed parameters
try:
max = Maximum.calculate(p_f.dbStrToArray(data))
# postgres requires return of an 'iterable object'
p_f_list.append(max)
except ValueError as ve:
log.warn ("max_power got ValueError, ignoring");
p_f_list.append(p_f("-1", "-1"))
return p_f_list
$$ LANGUAGE plpython3u IMMUTABLE;
-- sort x_t by times (ascending)
-- works with x_t_accum
CREATE OR REPLACE FUNCTION x_t_array_time_sort (data x_t[])
RETURNS x_t[] LANGUAGE SQL
AS $$
SELECT x_t_accum((d.value, d.time)::x_t) from
(select (unnest($1)).value as value, (unnest($1)).time as time order by time) as d;
$$;
--
-- EXAMPLE sql using these functions:
--
-- LSP calc
-- select calc_lsp(x_t_accum((mag,hjd)::x_t), 0.4, 0.6, 100) from test where objid = 1;
-- max frequency calc
-- select max_power (p_f_accum(result.p_f)) from (select calc_lsp(x_t_accum((mag,hjd)::x_t), 0.4, 0.6, 100) as p_f from test where objid = 1) as result;
--
--
-- EXPERIMENTAL FUNCTIONS
-- {try to make it more efficient)
--
-- to try to improve the interface for doing multi-argument
-- aggregate functions create special type for collecting function
-- params
create type lsp_input as (
data x_t,
f_low numeric,
f_high numeric,
f_bins integer
);
-- provides changing setof p_f into p_f[]
-- which is more convenient for bulk runs which
-- insert results from each run on a row in a
-- results table
create or replace function calc_lsp_arr_p_f(in lsp_input[])
returns p_f[]
AS $$
DEFINE
vals x_t[];
BEGIN
for data in $1
select array ( select calc_lsp(vals, $2, $3, $4) );
END;
$$ language sql immutable;
-- next two functions support creation of an
-- aggregate function to gather lsp_input(s)
create or replace function lsp_accum (t1 lsp_input[], t2 lsp_input)
returns lsp_input as
$$
select array_append($1, $2)::lsp_input[];
$$ language SQL immutable;
-- provides changing setof p_f into p_f[]
-- which is more convenient for bulk runs which
-- insert results from each run on a row in a
-- results table
create or replace function calc_lsp_arr_p_f(in lsp_input[])
returns p_f[]
AS $$
DEFINE
vals x_t[];
BEGIN
for data in $1
select array ( select calc_lsp(vals, $2, $3, $4) );
END;
$$ language sql immutable;
-- And finally, the actual function for calculation of LSPeriodogram.
-- in principle, we want an aggregate function.
-- but this is a bad idea, the finalfunc can only take
-- a single argument, which is of stype
-- OTOH, we might use SD, GD arrays to provide
-- things like f_high, f_over parameters to functions
--
-- see http://www.postgresql.org/docs/9.3/interactive/plpython-sharing.html
--
-- which is workable, but 'feels' wrong. Certainly a
-- violation of the 'functional' approach we want to take
CREATE AGGREGATE lsp (x_t, numeric, numeric, integer)
(
sfunc = lsp_accum,
stype = lsp_input,
finalfunc = calc_lsp_arr_p_f,
initcond = '{}'
);