Page History

...

WITH parameters AS 
    (SELECT 
    'law,ref' AS location_code_filter -- enter a location code; if left blank, the query will get all locations except for 'serv,remo'
    )

SELECT distinct
    ipd.instance_id,
    ipd.instance_hrid,
    instext.title,
    ll.location_name,
    he.call_number,
    ipd.physical_description,
    CASE
        WHEN substring (ipd.physical_description,'\d{1,4}') IS NOT NULL
        AND (ipd.physical_description LIKE '%page%' 
          OR ipd.physical_description LIKE '%p.%' 
          OR ipd.physical_description LIKE '%leaves%' 
          OR ipd.physical_description LIKE '% l.%')
        THEN substring (ipd.physical_description,'\d{1,4}')::INT
        ELSE NULL
    END AS page_count_estimate,
    ipd.physical_description_ordinality

FROM folio_derived.instance_ext AS instext
    LEFT JOIN folio_derived.holdings_ext AS he 
    ON instext.instance_id = he.instance_id
    
    LEFT JOIN folio_derived.locations_libraries AS ll 
    ON he.permanent_location_id = ll.location_id
    
    LEFT JOIN folio_derived.instance_physical_descriptions AS ipd 
    ON instext.instance_id = ipd.instance_id

WHERE 
    CASE WHEN (SELECT location_code_filter FROM parameters) =''
    THEN ll.location_code != 'serv,remo' 
    ELSE ll.location_code = (SELECT location_code_filter FROM parameters) 
    END
;

...