1 DROP SCHEMA reporter CASCADE;
3 CREATE SCHEMA reporter;
7 CREATE OR REPLACE VIEW reporter.date_series AS
8 SELECT CAST('1900/01/01' AS DATE) + x AS date,
9 CAST('1900/01/01' AS DATE) + x AS date_label
12 CAST( EXTRACT( 'days' FROM CAST( NOW() - CAST( '1900/01/01' AS DATE ) AS INTERVAL ) ) AS INT )
15 CREATE OR REPLACE VIEW reporter.date_hour_series AS
16 SELECT CAST(date + CAST(h || ' hours' AS INTERVAL) AS TIMESTAMP WITH TIME ZONE) AS date_hour,
17 CAST(date + CAST(h || ' hours' AS INTERVAL) AS TIMESTAMP WITH TIME ZONE) AS date_hour_label
18 FROM reporter.date_series,
19 GENERATE_SERIES(0,23) g(h);
21 CREATE TABLE reporter.stage2 (
22 id serial primary key,
28 create_date timestamp with time zone not null
30 edit_date timestamp with time zone not null
34 CREATE OR REPLACE FUNCTION reporter.force_edit_date_update () RETURNS TRIGGER AS $$
36 NEW.edit_date = NOW();
41 CREATE TRIGGER force_edit_date_update_trig
42 BEFORE UPDATE ON reporter.stage2
44 EXECUTE PROCEDURE reporter.force_edit_date_update ();
46 CREATE TABLE reporter.stage3 (
47 id serial primary key,
49 references reporter.stage2 (id)
57 create_date timestamp with time zone not null
59 runtime timestamp with time zone default now(),
63 CREATE TABLE reporter.output (
64 id serial primary key,
66 references reporter.stage3 (id)
70 queue_time timestamp with time zone not null default now(),
71 run_time timestamp with time zone,
75 error_time timestamp with time zone,
76 complete_time timestamp with time zone,
77 state text check (state in ('wait','running','complete','error'))