PRSCI

State code invalid

Change invalid state codes to NULL. Valid state codes are maintained in the public.state_codes table from published Regulations and Circulars.

-- © Copyright 2026 The Kahawai Collective
-- SPDX-License-Identifier: MIT
INSERT INTO grooming_rules
VALUES ('PRSCI', 'edw', 'processed_catch', 'Flag', 'Processed catch with invalid state code');

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

INSERT INTO groomed.checks(code, "table", "column", id, orig, new)
SELECT 'PRSCI', 'processed_catch', 'product_state_code', id, product_state_code, NULL
FROM   groomed.processed_catch
WHERE  product_state_code NOT IN (SELECT code FROM public.state_codes);

UPDATE groomed.processed_catch
SET    checks=array_append(checks,'PRSCI'),
       product_state_code = NULL
FROM   groomed.checks u
WHERE  u.code = 'PRSCI'
AND    groomed.processed_catch.id = u.id
AND    groomed.processed_catch.product_state_code = u.orig;

$$ LANGUAGE SQL;

SELECT PRSCI();

Effects of grooming

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

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