gemma: schema/updates/1469/aggregate-gm-logs.sql annotate

annotate schema/updates/1469/aggregate-gm-logs.sql @ 5541:29804c8e817d aggregate-gm-import-logging

WIP: Handle rest correctly.

author	Sascha L. Teichmann <sascha.teichmann@intevation.de>
date	Tue, 26 Oct 2021 02:02:23 +0200
parents	4d815f295e57
children	0acb06fc77e6

rev	line source
5534 cb62985ec252 WIP: Started with migration to convert all gm logs to new form. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	1 DO $$
cb62985ec252 WIP: Started with migration to convert all gm logs to new form. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	2 DECLARE
5540 4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	3 logs NO SCROLL CURSOR FOR SELECT
5534 cb62985ec252 WIP: Started with migration to convert all gm logs to new form. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	4 lo.* FROM import.imports im JOIN import.import_logs lo
cb62985ec252 WIP: Started with migration to convert all gm logs to new form. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	5 ON lo.import_id = im.id
cb62985ec252 WIP: Started with migration to convert all gm logs to new form. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	6 WHERE im.kind = 'gm'
cb62985ec252 WIP: Started with migration to convert all gm logs to new form. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	7 ORDER BY lo.import_id, lo.time;
cb62985ec252 WIP: Started with migration to convert all gm logs to new form. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	8 last_id integer;
5535 1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	9 last_time timestamp with time zone;
1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	10 curr_gid text;
5538 ff95d3603e4d WIP: More aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5536 diff changeset	11 value text;
5536 dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	12 joined text;
5535 1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	13 num integer;
5541 29804c8e817d WIP: Handle rest correctly. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5540 diff changeset	14 line record;
29804c8e817d WIP: Handle rest correctly. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5540 diff changeset	15 hold record;
29804c8e817d WIP: Handle rest correctly. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5540 diff changeset	16 done boolean;
5534 cb62985ec252 WIP: Started with migration to convert all gm logs to new form. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	17 BEGIN
cb62985ec252 WIP: Started with migration to convert all gm logs to new form. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	18
cb62985ec252 WIP: Started with migration to convert all gm logs to new form. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	19 CREATE TEMP TABLE filtered_logs (
cb62985ec252 WIP: Started with migration to convert all gm logs to new form. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	20 import_id integer NOT NULL,
cb62985ec252 WIP: Started with migration to convert all gm logs to new form. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	21 time timestamp with time zone NOT NULL,
cb62985ec252 WIP: Started with migration to convert all gm logs to new form. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	22 kind log_type NOT NULL default 'info'::log_type,
cb62985ec252 WIP: Started with migration to convert all gm logs to new form. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	23 msg text NOT NULL
cb62985ec252 WIP: Started with migration to convert all gm logs to new form. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	24 );
cb62985ec252 WIP: Started with migration to convert all gm logs to new form. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	25
cb62985ec252 WIP: Started with migration to convert all gm logs to new form. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	26 CREATE TEMP TABLE agg_tracker (
cb62985ec252 WIP: Started with migration to convert all gm logs to new form. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	27 gid text NOT NULL UNIQUE,
cb62985ec252 WIP: Started with migration to convert all gm logs to new form. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	28 unknown boolean NOT NULL DEFAULT false,
cb62985ec252 WIP: Started with migration to convert all gm logs to new form. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	29 assume_zpg boolean NOT NULL DEFAULT false,
cb62985ec252 WIP: Started with migration to convert all gm logs to new form. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	30 ign_meas_codes text ARRAY,
cb62985ec252 WIP: Started with migration to convert all gm logs to new form. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	31 rescale_errors text ARRAY,
cb62985ec252 WIP: Started with migration to convert all gm logs to new form. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	32 missing_values text ARRAY,
cb62985ec252 WIP: Started with migration to convert all gm logs to new form. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	33 assume_cm integer NOT NULL DEFAULT 0,
cb62985ec252 WIP: Started with migration to convert all gm logs to new form. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	34 bad_values integer NOT NULL DEFAULT 0,
cb62985ec252 WIP: Started with migration to convert all gm logs to new form. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	35 measurements integer NOT NULL DEFAULT 0,
cb62985ec252 WIP: Started with migration to convert all gm logs to new form. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	36 predictions integer NOT NULL DEFAULT 0
cb62985ec252 WIP: Started with migration to convert all gm logs to new form. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	37 );
5541 29804c8e817d WIP: Handle rest correctly. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5540 diff changeset	38
5534 cb62985ec252 WIP: Started with migration to convert all gm logs to new form. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	39 last_id := -1;
5541 29804c8e817d WIP: Handle rest correctly. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5540 diff changeset	40 done := false;
29804c8e817d WIP: Handle rest correctly. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5540 diff changeset	41 hold := NULL;
29804c8e817d WIP: Handle rest correctly. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5540 diff changeset	42
29804c8e817d WIP: Handle rest correctly. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5540 diff changeset	43 OPEN logs;
5535 1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	44
5541 29804c8e817d WIP: Handle rest correctly. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5540 diff changeset	45 LOOP
29804c8e817d WIP: Handle rest correctly. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5540 diff changeset	46
29804c8e817d WIP: Handle rest correctly. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5540 diff changeset	47 FETCH logs INTO line;
29804c8e817d WIP: Handle rest correctly. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5540 diff changeset	48
29804c8e817d WIP: Handle rest correctly. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5540 diff changeset	49 IF NOT FOUND THEN
29804c8e817d WIP: Handle rest correctly. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5540 diff changeset	50 done := true;
29804c8e817d WIP: Handle rest correctly. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5540 diff changeset	51 END IF;
5535 1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	52
5540 4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	53 -- RAISE NOTICE '%', line.msg;
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	54
5541 29804c8e817d WIP: Handle rest correctly. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5540 diff changeset	55 IF done OR last_id <> line.import_id THEN
5536 dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	56
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	57 -- unknown
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	58 SELECT INTO joined string_agg(gid, ', ')
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	59 FROM agg_tracker
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	60 WHERE unknown;
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	61
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	62 IF joined IS NOT NULL THEN
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	63 INSERT INTO filtered_logs VALUES (
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	64 last_id, last_time, 'warn'::log_type,
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	65 'Cannot find following gauges: ' \|\| joined);
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	66 END IF;
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	67
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	68 -- assume zpg
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	69 SELECT INTO joined string_agg(gid, ', ')
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	70 FROM agg_tracker
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	71 WHERE assume_zpg;
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	72
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	73 IF joined IS NOT NULL THEN
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	74 INSERT INTO filtered_logs VALUES (
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	75 last_id, last_time, 'warn'::log_type,
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	76 '''Reference_code'' not specified. Assuming ''ZPG'': ' \|\| joined);
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	77 END IF;
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	78
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	79 -- assume cm
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	80 SELECT INTO joined string_agg(
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	81 gid \|\| ' (' \|\| assume_cm \|\| ')', ', ')
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	82 FROM agg_tracker
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	83 WHERE assume_cm > 0;
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	84
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	85 IF joined IS NOT NULL THEN
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	86 INSERT INTO filtered_logs VALUES (
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	87 last_id, last_time, 'warn'::log_type,
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	88 '''Unit'' not specified. Assuming ''cm'': ' \|\| joined);
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	89 END IF;
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	90
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	91 -- bad values
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	92 SELECT INTO joined string_agg(
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	93 gid \|\| ' (' \|\| bad_values \|\| ')', ', ')
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	94 FROM agg_tracker
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	95 WHERE bad_values > 0;
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	96
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	97 IF joined IS NOT NULL THEN
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	98 INSERT INTO filtered_logs VALUES (
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	99 last_id, last_time, 'warn'::log_type,
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	100 'Ignored measurements with value -99999: ' \|\| joined);
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	101 END IF;
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	102
5540 4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	103 -- missing mandatory values
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	104 SELECT INTO joined string_agg(
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	105 gid \|\| ' ('
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	106 \|\| array_to_string(ARRAY(SELECT DISTINCT unnest(missing_values)), '; ')
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	107 \|\| ')', ', ')
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	108 FROM agg_tracker
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	109 WHERE missing_values IS NOT NULL;
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	110
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	111 IF joined IS NOT NULL THEN
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	112 INSERT INTO filtered_logs VALUES (
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	113 last_id, last_time, 'warn'::log_type,
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	114 'Missing mandatory values: ' \|\| joined);
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	115 END IF;
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	116
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	117 -- convert units
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	118 SELECT INTO joined string_agg(
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	119 gid \|\| ' ('
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	120 \|\| array_to_string(ARRAY(SELECT DISTINCT unnest(rescale_errors)), '; ')
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	121 \|\| ')', ', ')
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	122 FROM agg_tracker
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	123 WHERE rescale_errors IS NOT NULL;
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	124
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	125 IF joined IS NOT NULL THEN
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	126 INSERT INTO filtered_logs VALUES (
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	127 last_id, last_time, 'error'::log_type,
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	128 'Cannot convert units: ' \|\| joined);
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	129 END IF;
5538 ff95d3603e4d WIP: More aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5536 diff changeset	130
ff95d3603e4d WIP: More aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5536 diff changeset	131 -- ignored measure codes
ff95d3603e4d WIP: More aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5536 diff changeset	132 SELECT INTO joined string_agg(
ff95d3603e4d WIP: More aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5536 diff changeset	133 gid \|\| ' ('
ff95d3603e4d WIP: More aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5536 diff changeset	134 \|\| array_to_string(ARRAY(SELECT DISTINCT unnest(ign_meas_codes)), '; ')
ff95d3603e4d WIP: More aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5536 diff changeset	135 \|\| ')', ', ')
ff95d3603e4d WIP: More aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5536 diff changeset	136 FROM agg_tracker
ff95d3603e4d WIP: More aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5536 diff changeset	137 WHERE ign_meas_codes IS NOT NULL;
ff95d3603e4d WIP: More aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5536 diff changeset	138
ff95d3603e4d WIP: More aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5536 diff changeset	139 IF joined IS NOT NULL THEN
ff95d3603e4d WIP: More aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5536 diff changeset	140 INSERT INTO filtered_logs VALUES (
ff95d3603e4d WIP: More aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5536 diff changeset	141 last_id, last_time, 'warn'::log_type,
ff95d3603e4d WIP: More aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5536 diff changeset	142 'Ignored measure codes: ' \|\| joined);
ff95d3603e4d WIP: More aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5536 diff changeset	143 END IF;
5536 dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	144
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	145 -- predictions
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	146 SELECT INTO joined string_agg(
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	147 gid \|\| ' (' \|\| predictions \|\| ')', ', ')
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	148 FROM agg_tracker
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	149 WHERE predictions > 0;
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	150
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	151 IF joined IS NOT NULL THEN
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	152 INSERT INTO filtered_logs VALUES (
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	153 last_id, last_time, 'info'::log_type,
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	154 'New predictions: ' \|\| joined);
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	155 END IF;
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	156
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	157 -- measurements
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	158 SELECT INTO joined string_agg(
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	159 gid \|\| ' (' \|\| measurements \|\| ')', ', ')
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	160 FROM agg_tracker
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	161 WHERE measurements > 0;
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	162
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	163 IF joined IS NOT NULL THEN
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	164 INSERT INTO filtered_logs VALUES (
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	165 last_id, last_time, 'info'::log_type,
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	166 'New measurements: ' \|\| joined);
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	167 END IF;
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	168
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	169 -- nothing changed
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	170 SELECT INTO joined string_agg(gid, ', ')
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	171 FROM agg_tracker
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	172 WHERE measurements = 0 and predictions = 0;
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	173
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	174 IF joined IS NOT NULL THEN
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	175 INSERT INTO filtered_logs VALUES (
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	176 last_id, last_time, 'info'::log_type,
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	177 'No changes for: ' \|\| joined);
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	178 END IF;
dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	179
5541 29804c8e817d WIP: Handle rest correctly. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5540 diff changeset	180 IF hold is NOT NULL THEN
29804c8e817d WIP: Handle rest correctly. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5540 diff changeset	181 INSERT INTO filtered_logs VALUES (hold.import_id, hold.time, hold.kind, hold.msg);
29804c8e817d WIP: Handle rest correctly. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5540 diff changeset	182 hold := NULL;
29804c8e817d WIP: Handle rest correctly. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5540 diff changeset	183 END IF;
29804c8e817d WIP: Handle rest correctly. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5540 diff changeset	184
29804c8e817d WIP: Handle rest correctly. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5540 diff changeset	185 IF done THEN
29804c8e817d WIP: Handle rest correctly. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5540 diff changeset	186 EXIT;
29804c8e817d WIP: Handle rest correctly. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5540 diff changeset	187 END IF;
5536 dc2000b807bd WIP: More on aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5535 diff changeset	188
5540 4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	189 -- reset aggregate table
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	190 TRUNCATE agg_tracker;
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	191 last_id := line.import_id;
5535 1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	192
1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	193 ELSIF line.msg ~ '^Found measurements/predictions for ....................$' THEN
1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	194 curr_gid := substring(
1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	195 line.msg from '^Found measurements/predictions for (....................)$');
1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	196
1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	197 ELSIF line.msg ~ '^Inserted \d+ measurements for ....................$' THEN
1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	198 num := substring(
1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	199 line.msg from '^Inserted (\d+)')::integer;
5538 ff95d3603e4d WIP: More aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5536 diff changeset	200 value := substring(
5535 1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	201 line.msg from '^Inserted \d+ measurements for (....................)$');
1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	202
5538 ff95d3603e4d WIP: More aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5536 diff changeset	203 INSERT INTO agg_tracker (gid, measurements) VALUES (value, num)
5535 1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	204 ON CONFLICT (gid)
1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	205 DO UPDATE SET measurements = EXCLUDED.measurements + num;
1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	206
1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	207 ELSIF line.msg ~ '^Inserted \d+ predictions for ....................$' THEN
1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	208 num := substring(
1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	209 line.msg from '^Inserted (\d+)')::integer;
5538 ff95d3603e4d WIP: More aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5536 diff changeset	210 value := substring(
5535 1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	211 line.msg from '^Inserted \d+ predictions for (....................)$');
1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	212
5538 ff95d3603e4d WIP: More aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5536 diff changeset	213 INSERT INTO agg_tracker (gid, predictions) VALUES (value, num)
5535 1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	214 ON CONFLICT (gid)
1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	215 DO UPDATE SET predictions = EXCLUDED.predictions + num;
1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	216
1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	217 ELSIF line.msg ~ '^''Reference_code'' not specified. Assuming ''ZPG''$' THEN
1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	218
5538 ff95d3603e4d WIP: More aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5536 diff changeset	219 INSERT INTO agg_tracker (gid, assume_zpg) VALUES (curr_gid, true)
5535 1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	220 ON CONFLICT (gid)
1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	221 DO UPDATE SET assume_zpg = true;
1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	222
5541 29804c8e817d WIP: Handle rest correctly. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5540 diff changeset	223 ELSIF line.msg ~ '^Ignored \d+ measurements with value -99999$' THEN
29804c8e817d WIP: Handle rest correctly. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5540 diff changeset	224 num := substring(
29804c8e817d WIP: Handle rest correctly. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5540 diff changeset	225 line.msg from '^Ignored (\d+)')::integer;
29804c8e817d WIP: Handle rest correctly. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5540 diff changeset	226
29804c8e817d WIP: Handle rest correctly. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5540 diff changeset	227 INSERT INTO agg_tracker (gid, bad_values) VALUES (curr_gid, num)
29804c8e817d WIP: Handle rest correctly. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5540 diff changeset	228 ON CONFLICT (gid)
29804c8e817d WIP: Handle rest correctly. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5540 diff changeset	229 DO UPDATE SET bad_values = EXCLUDED.bad_values + num;
29804c8e817d WIP: Handle rest correctly. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5540 diff changeset	230
5535 1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	231 ELSIF line.msg ~ '^Cannot find gauge "...................." for import$' THEN
5538 ff95d3603e4d WIP: More aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5536 diff changeset	232 value := substring(
5535 1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	233 line.msg from '^Cannot find gauge "(....................)" for import$');
1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	234
5538 ff95d3603e4d WIP: More aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5536 diff changeset	235 INSERT INTO agg_tracker (gid, unknown) VALUES (value, true)
5535 1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	236 ON CONFLICT (gid)
1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	237 DO UPDATE SET unknown = true;
1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	238
1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	239 ELSIF line.msg ~ '^''Unit'' not specified. Assuming ''cm''$' THEN
1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	240
5538 ff95d3603e4d WIP: More aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5536 diff changeset	241 INSERT INTO agg_tracker (gid, assume_cm) VALUES (curr_gid, 1)
5535 1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	242 ON CONFLICT (gid)
1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	243 DO UPDATE SET assume_cm = EXCLUDED.assume_cm + 1;
1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	244
5538 ff95d3603e4d WIP: More aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5536 diff changeset	245 ELSIF line.msg ~ '^Ignored message with measure_code .+' THEN
ff95d3603e4d WIP: More aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5536 diff changeset	246 value := substring(
ff95d3603e4d WIP: More aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5536 diff changeset	247 line.msg from '^Ignored message with measure_code (.+)$');
ff95d3603e4d WIP: More aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5536 diff changeset	248
ff95d3603e4d WIP: More aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5536 diff changeset	249 INSERT INTO agg_tracker (gid, ign_meas_codes) VALUES (curr_gid, array[value])
ff95d3603e4d WIP: More aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5536 diff changeset	250 ON CONFLICT (gid)
ff95d3603e4d WIP: More aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5536 diff changeset	251 DO UPDATE SET ign_meas_codes =
ff95d3603e4d WIP: More aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5536 diff changeset	252 array_cat(agg_tracker.ign_meas_codes, EXCLUDED.ign_meas_codes);
ff95d3603e4d WIP: More aggregation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5536 diff changeset	253
5540 4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	254 ELSIF line.msg ~ '^Missing mandatory value at [^.]+\.' THEN
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	255 value := substring(
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	256 line.msg from '^Missing mandatory value at ([^.]+)\.');
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	257
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	258 INSERT INTO agg_tracker (gid, missing_values) VALUES (curr_gid, array[value])
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	259 ON CONFLICT (gid)
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	260 DO UPDATE SET missing_values =
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	261 array_cat(agg_tracker.missing_values, EXCLUDED.missing_values);
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	262
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	263 ELSIF line.msg ~ '^unknown unit ''[^'']*''' THEN
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	264
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	265 value := substring(
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	266 line.msg from '^unknown unit ''([^'']*)''');
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	267
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	268 INSERT INTO agg_tracker (gid, rescale_errors) VALUES (curr_gid, array[value])
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	269 ON CONFLICT (gid)
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	270 DO UPDATE SET rescale_errors =
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	271 array_cat(agg_tracker.rescale_errors, EXCLUDED.rescale_errors);
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	272
5535 1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	273 ELSIF line.msg ~ '^Importing gauge measurements took ' THEN
5541 29804c8e817d WIP: Handle rest correctly. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5540 diff changeset	274 -- Likely the last entry of this import.
29804c8e817d WIP: Handle rest correctly. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5540 diff changeset	275 hold := line;
5535 1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	276 ELSE
1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	277 -- Not handled, copy through ..
5541 29804c8e817d WIP: Handle rest correctly. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5540 diff changeset	278 -- RAISE NOTICE '%', line.msg;
5535 1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	279 INSERT INTO filtered_logs VALUES (line.import_id, line.time, line.kind, line.msg);
1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	280
5534 cb62985ec252 WIP: Started with migration to convert all gm logs to new form. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	281 END IF;
5535 1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	282
1fba5766d820 WIP: More on migration script. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5534 diff changeset	283 last_time := line.time;
5534 cb62985ec252 WIP: Started with migration to convert all gm logs to new form. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	284 END LOOP;
cb62985ec252 WIP: Started with migration to convert all gm logs to new form. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	285
cb62985ec252 WIP: Started with migration to convert all gm logs to new form. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	286 -- DELETE FROM import.import_logs WHERE import_id IN (
cb62985ec252 WIP: Started with migration to convert all gm logs to new form. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	287 -- SELECT id FROM import.imports WHERE kind = 'gm')
cb62985ec252 WIP: Started with migration to convert all gm logs to new form. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	288
cb62985ec252 WIP: Started with migration to convert all gm logs to new form. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	289 -- INSERT INTO import.import_logs SELECT * FROM import.filtered_logs;
cb62985ec252 WIP: Started with migration to convert all gm logs to new form. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	290
5540 4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	291 SELECT INTO num count(*) FROM filtered_logs;
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	292
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	293 RAISE NOTICE 'number of new gm log message entries: %', num;
4d815f295e57 WIP: Removed more TODOs. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5538 diff changeset	294
5534 cb62985ec252 WIP: Started with migration to convert all gm logs to new form. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	295 DROP TABLE filtered_logs;
cb62985ec252 WIP: Started with migration to convert all gm logs to new form. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	296 DROP TABLE agg_tracker;
cb62985ec252 WIP: Started with migration to convert all gm logs to new form. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	297 END $$;
cb62985ec252 WIP: Started with migration to convert all gm logs to new form. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	298 -- VACUUM FULL;
cb62985ec252 WIP: Started with migration to convert all gm logs to new form. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	299
cb62985ec252 WIP: Started with migration to convert all gm logs to new form. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	300

Mercurial > gemma

annotate schema/updates/1469/aggregate-gm-logs.sql @ 5541:29804c8e817d aggregate-gm-import-logging