x
1
DECLARE
2
modified_count integer;
3
remaining_count integer;
4
BEGIN
5
6
-- Update the observations
7
WITH problem AS (
8
SELECT *
9
FROM gobs.find_observation_with_wrong_spatial_object(_id_series)
10
), solution AS (
11
SELECT
12
o.ob_uid, so.id AS new_id
13
FROM
14
gobs.observation AS o,
15
problem AS p,
16
gobs.spatial_object AS so
17
WHERE True
18
-- same unique id as problematic
19
AND p.so_unique_id = so.so_unique_id
20
-- not itself
21
AND True
22
-- problematic only
23
AND p.ob_uid = o.ob_uid
24
-- same series
25
AND o.fk_id_series = _id_series
26
-- same spatial layer
27
AND so.fk_id_spatial_layer IN (
28
SELECT fk_id_spatial_layer
29
FROM gobs.series
30
WHERE id = _id_series
31
)
32
-- compatible dates
33
AND
34
ob_start_timestamp
35
BETWEEN Coalesce(so.so_valid_from, '-infinity'::timestamp) AND Coalesce(so.so_valid_to, 'infinity'::timestamp)
36
AND (
37
ob_end_timestamp IS NULL
38
OR
39
( ob_end_timestamp IS NOT NULL
40
AND ob_end_timestamp
41
BETWEEN Coalesce(so_valid_from, '-infinity'::timestamp) AND Coalesce(so_valid_to, 'infinity'::timestamp)
42
)
43
)
44
)
45
UPDATE gobs.observation oo
46
SET fk_id_spatial_object = s.new_id
47
FROM solution AS s
48
WHERE oo.ob_uid = s.ob_uid
49
;
50
51
-- Get the number of rows updated
52
GET DIAGNOSTICS modified_count = ROW_COUNT;
53
54
-- Check if there is anything remaining
55
-- for example if no new id have been found to replace the wrong ones
56
SELECT count(*)
57
FROM gobs.find_observation_with_wrong_spatial_object(_id_series)
58
INTO remaining_count;
59
60
RETURN QUERY SELECT modified_count, remaining_count;
61
62
END;