Revision 3746
Added by Aaron Marcuse-Kubitza over 12 years ago
| schemas/vegbien.sql | ||
|---|---|---|
| 286 | 286 |
CREATE FUNCTION plantobservation_aggregateoccurrence_count_1() RETURNS trigger |
| 287 | 287 |
LANGUAGE plpgsql |
| 288 | 288 |
AS $$ |
| 289 |
DECLARE |
|
| 290 |
count integer := ( |
|
| 291 |
SELECT count |
|
| 292 |
FROM aggregateoccurrence |
|
| 293 |
WHERE aggregateoccurrence_id = new.aggregateoccurrence_id |
|
| 294 |
); |
|
| 295 | 289 |
BEGIN |
| 296 |
IF NOT (count IS NULL OR count = 1) THEN |
|
| 297 |
RAISE 'aggregateoccurrence.count must be NULL or 1, but was %', count; |
|
| 298 |
END IF; |
|
| 299 | 290 |
UPDATE aggregateoccurrence |
| 300 |
SET count = 1 |
|
| 291 |
SET count = GREATEST(COALESCE(count, 0), 1) -- at least 1
|
|
| 301 | 292 |
WHERE aggregateoccurrence_id = new.aggregateoccurrence_id |
| 302 | 293 |
; |
| 303 | 294 |
RETURN new; |
Also available in: Unified diff
schemas/vegbien.sql: plantobservation: plantobservation_aggregateoccurrence_count_1() trigger: Don't raise an error if existing count was >1, because there are in fact datasets (notably SALVIAS) where input records for individual stems may themselves contain aggregate data (such as plant and stem counts). For this data, we have an anomalous condition where an aggregateoccurrence has count >1 but contains one plantobservation, due to the plant/stem count being included in the first stem's record. (See <https://projects.nceas.ucsb.edu/nceas/projects/bien/wiki/SALVIAS_issues#Data-interpretation-issues> for more info on this problem.) Note that our desired 1:1 relationship between aggregateoccurrence and plantobservation is still guaranteed by a constraint, but the anomalous data may still cause irregularities later on in the analysis.