LAHPB

Fishstock code is for a groper species

Occasionally (and now exclusively) groper landings are recorded using the species specific code (HAP or BAS) instead of the general groper code (HPB). This check is done early so that total groper landings are checked in subsequent landings checks.

-- © Copyright 2026 The Kahawai Collective
-- SPDX-License-Identifier: MIT
INSERT INTO grooming_rules
VALUES ('LAHPB', 'edw', 'landings', 'Fix', 'Correct landings using a groper species code to HPB');

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

INSERT INTO groomed.checks(code, "table", "column", id, orig, new)
SELECT 'LAHPB', 'landings', 'species_code', id, species_code, 'HPB'
FROM   groomed.landings
WHERE  substring(species_code from 1 for 3) IN ('BAS','HAP');

INSERT INTO groomed.checks(code, "table", "column", id, orig, new)
SELECT 'LAHPB', 'landings', 'fishstock_code', id, fishstock_code,
       'HPB' || substring(fishstock_code from 4 for 2)
FROM   groomed.landings
WHERE  substring(fishstock_code from 1 for 3) IN ('BAS','HAP');

UPDATE groomed.landings
SET    checks = array_append(checks,'LAHPB'),
       species_code = u.new
FROM   groomed.checks u
WHERE  u.code = 'LAHPB'
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,'LAHPB'),
       fishstock_code = u.new
FROM   groomed.checks u
WHERE  u.code = 'LAHPB'
AND    u.column = 'fishstock_code'
AND    groomed.landings.id = u.id
AND    groomed.landings.fishstock_code = u.orig;

$$ LANGUAGE SQL VOLATILE;

SELECT LAHPB();

Effects of grooming

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

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