LAOEO

Fishstock code is for an oreo species

Occasionally (and now exclusively) oreo landings are recorded using the species specific code (e.g. BOE) instead of the general oreo code (OEO). This check is done early so that total oreo landings are checked in subsequent landings checks.

-- © Copyright 2026 The Kahawai Collective
-- SPDX-License-Identifier: MIT
INSERT INTO grooming_rules
VALUES ('LAOEO', 'edw', 'landings', 'Fix', 'Correct landings using an oreo species code to OEO');

CREATE OR REPLACE FUNCTION LAOEO() RETURNS VOID AS $$

INSERT INTO groomed.checks(code, "table", "column", id, orig, new)
SELECT 'LAOEO', 'landings', 'species_code', id, species_code, 'OEO'
FROM   groomed.landings
WHERE  substring(species_code from 1 for 3) IN ('BOE', 'SSO', 'SOR', 'WOE');

INSERT INTO groomed.checks(code, "table", "column", id, orig, new)
SELECT 'LAOEO', 'landings', 'fishstock_code', id, fishstock_code,
       'OEO' || substring(fishstock_code from 4 for 2)
FROM   groomed.landings
WHERE  substring(fishstock_code from 1 for 3) IN ('BOE', 'SSO', 'SOR', 'WOE');

UPDATE groomed.landings
SET    checks = array_append(checks,'LAOEO'),
       species_code = u.new
FROM   groomed.checks u
WHERE  u.code = 'LAOEO'
AND    u.column = 'species_code'
AND    groomed.landings.id = u.id
AND    groomed.landings.species_code = u.orig;

UPDATE groomed.landings
SET    checks = array_append(checks,'LAOEO'),
       fishstock_code = u.new
FROM   groomed.checks u
WHERE  u.code = 'LAOEO'
AND    u.column = 'fishstock_code'
AND    groomed.landings.id = u.id
AND    groomed.landings.fishstock_code = u.orig;

$$ LANGUAGE SQL VOLATILE;

SELECT LAOEO();

Effects of grooming

Figure 1: The number of records affected by the LAOEO rule, by year and source.

Figure 2: The proportion of records affected by the LAOEO rule, by form type, for all form types where some records are groomed by the LAOEO rule.