PRSCD

State codes for secondary product states

Starr (2007) suggested dropping “landings where state code==FIN|==FLP|==SHF|==ROE and there is more than one record for the trip/Fishstock combination.”

The current implementation drops any record of a secondary product state, based on the list of secondary state codes maintained in the public.state_codes table from published Regulations and Circulars.

Processed catches of secondary states are only dropped where primary states for the species exist on a trip, to guard against cases where the secondary state code is used in error and, therefore, be retained as the best record of catch from the trip.

-- © Copyright 2026 The Kahawai Collective
-- SPDX-License-Identifier: MIT
CREATE TEMP TABLE _trip_species_principal AS
SELECT DISTINCT trip, species_code
  FROM groomed.processed_catch
 WHERE product_state_code IN (SELECT code FROM public.state_codes WHERE state_type = 'principal');
                
INSERT INTO grooming_rules
VALUES ('PRSCD', 'edw', 'processed_catch', 'Drop', 'Drop processed catch of secondary product states');

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

INSERT INTO groomed.checks(code, "table", "column", id)
SELECT 'PRSCD', 'processed_catch', 'product_state_code', l.id
  FROM groomed.processed_catch l
  JOIN _trip_species_principal p USING (trip, species_code)
 WHERE l.product_state_code IN (SELECT code FROM public.state_codes WHERE state_type = 'secondary');

UPDATE groomed.processed_catch
   SET checks = array_append(checks, 'PRSCD'), dropped=TRUE
  FROM groomed.checks u
 WHERE u.code = 'PRSCD'
   AND groomed.processed_catch.id = u.id;

$$ LANGUAGE SQL;

SELECT PRSCD();

Effects of grooming

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