89 lines
2.6 KiB
PL/PgSQL
89 lines
2.6 KiB
PL/PgSQL
create table pv_power_measurement_t (
|
|
time timestamp without time zone not null,
|
|
deviceid text,
|
|
status text,
|
|
state integer,
|
|
importEnergyActive double precision,
|
|
importEnergyReactive double precision,
|
|
exportEnergyActive double precision,
|
|
exportEnergyReactive double precision,
|
|
powerApparent double precision,
|
|
powerActive double precision,
|
|
powerReactive double precision,
|
|
powerDemandPositive double precision,
|
|
powerDemandReverse double precision,
|
|
powerDemand double precision,
|
|
factor double precision,
|
|
angle double precision,
|
|
voltage double precision,
|
|
current double precision
|
|
);
|
|
|
|
select create_hypertable('pv_power_measurement_t', 'time');
|
|
|
|
grant insert on pv_power_measurement_t to nodered;
|
|
grant select on pv_power_measurement_t to grafana;
|
|
|
|
create view pv_stats_v as
|
|
select time, importEnergyActive, importEnergyReactive, exportEnergyActive, exportEnergyReactive,
|
|
powerApparent, powerActive, powerReactive, powerDemandPositive, powerDemandReverse, powerDemand,
|
|
factor, angle, voltage, current
|
|
from pv_power_measurement_t
|
|
order by time;
|
|
|
|
|
|
create table pv_stats_t (
|
|
id serial not null primary key,
|
|
"date" date not null,
|
|
dateType varchar(5) not null,
|
|
first numeric(10,2) not null default 0,
|
|
total numeric(10,2) not null default 0
|
|
);
|
|
alter table pv_stats_t add constraint ddT_uk unique ("date", dateType);
|
|
|
|
grant insert, select, update on pv_stats_t to nodered;
|
|
grant select, update on pv_stats_t_id_seq to nodered;
|
|
|
|
create or replace function pv_stats_func ()
|
|
returns trigger
|
|
language plpgsql
|
|
as $$
|
|
declare
|
|
v_stat_id pv_stats_t.id%TYPE;
|
|
v_dateTypes varchar[] := array['day', 'month', 'year'];
|
|
v_dateType varchar;
|
|
begin
|
|
foreach v_dateType in array v_dateTypes
|
|
loop
|
|
select id
|
|
from pv_stats_t
|
|
into v_stat_id
|
|
where "date" = date_trunc(v_dateType, NEW.time::date) and
|
|
dateType = v_dateType;
|
|
if not found then
|
|
insert into pv_stats_t ("date", dateType, first)
|
|
values (date_trunc(v_dateType, NEW.time::date), v_dateType, NEW.exportEnergyActive);
|
|
else
|
|
update pv_stats_t
|
|
set total = NEW.exportEnergyActive - first
|
|
where id = v_stat_id;
|
|
end if;
|
|
|
|
end loop;
|
|
|
|
return NEW;
|
|
end;
|
|
$$
|
|
|
|
create trigger pv_stats_trig
|
|
after insert on pv_power_measurement_t
|
|
for each row
|
|
execute function pv_stats_func();
|
|
|
|
|
|
|
|
insert into pv_stats_t("date", dateType, first, total) values (date_trunc('month', now()), 'month', 0.01, 0)
|
|
on conflict on constraint ddT_uk do update set total = 3.26 - excluded.first;
|
|
|
|
;
|