From c6c03f936451a110a8ae227d10f4452d14f77a62 Mon Sep 17 00:00:00 2001 From: miker Date: Fri, 11 Feb 2011 00:49:40 +0000 Subject: [PATCH] Postgres 9.0 is more strict about variable names in functions. We will probably hit more of these, but these in particular break the OPAC. git-svn-id: svn://svn.open-ils.org/ILS/trunk@19427 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/040.schema.asset.sql | 68 ++-- .../sql/Pg/upgrade/0484.sql.pg-90-compat.sql | 308 ++++++++++++++++++ 3 files changed, 343 insertions(+), 35 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0484.sql.pg-90-compat.sql diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 353394ece8..bf3107d0bb 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -70,7 +70,7 @@ CREATE TABLE config.upgrade_log ( install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); -INSERT INTO config.upgrade_log (version) VALUES ('0483'); -- miker +INSERT INTO config.upgrade_log (version) VALUES ('0484'); -- miker CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/040.schema.asset.sql b/Open-ILS/src/sql/Pg/040.schema.asset.sql index 623b7f1278..cb738963e3 100644 --- a/Open-ILS/src/sql/Pg/040.schema.asset.sql +++ b/Open-ILS/src/sql/Pg/040.schema.asset.sql @@ -390,12 +390,12 @@ CREATE VIEW stats.fleshed_call_number AS FROM asset.call_number cn JOIN metabib.rec_descriptor rd ON (rd.record = cn.record); -CREATE OR REPLACE FUNCTION asset.opac_ou_record_copy_count (org INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +CREATE OR REPLACE FUNCTION asset.opac_ou_record_copy_count (org INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ DECLARE ans RECORD; trans INT; BEGIN - SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = record; + SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid; FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP RETURN QUERY @@ -407,7 +407,7 @@ BEGIN trans FROM actor.org_unit_descendants(ans.id) d - JOIN asset.opac_visible_copies av ON (av.record = record AND av.circ_lib = d.id) + JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id) JOIN asset.copy cp ON (cp.id = av.id) GROUP BY 1,2,6; @@ -421,12 +421,12 @@ BEGIN END; $f$ LANGUAGE PLPGSQL; -CREATE OR REPLACE FUNCTION asset.opac_lasso_record_copy_count (i_lasso INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +CREATE OR REPLACE FUNCTION asset.opac_lasso_record_copy_count (i_lasso INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ DECLARE ans RECORD; trans INT; BEGIN - SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = record; + SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid; FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP RETURN QUERY @@ -438,7 +438,7 @@ BEGIN trans FROM actor.org_unit_descendants(ans.id) d - JOIN asset.opac_visible_copies av ON (av.record = record AND av.circ_lib = d.id) + JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id) JOIN asset.copy cp ON (cp.id = av.id) GROUP BY 1,2,6; @@ -452,12 +452,12 @@ BEGIN END; $f$ LANGUAGE PLPGSQL; -CREATE OR REPLACE FUNCTION asset.staff_ou_record_copy_count (org INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +CREATE OR REPLACE FUNCTION asset.staff_ou_record_copy_count (org INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ DECLARE ans RECORD; trans INT; BEGIN - SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = record; + SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid; FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP RETURN QUERY @@ -470,7 +470,7 @@ BEGIN FROM actor.org_unit_descendants(ans.id) d JOIN asset.copy cp ON (cp.circ_lib = d.id) - JOIN asset.call_number cn ON (cn.record = record AND cn.id = cp.call_number) + JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number) GROUP BY 1,2,6; IF NOT FOUND THEN @@ -483,12 +483,12 @@ BEGIN END; $f$ LANGUAGE PLPGSQL; -CREATE OR REPLACE FUNCTION asset.staff_lasso_record_copy_count (i_lasso INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +CREATE OR REPLACE FUNCTION asset.staff_lasso_record_copy_count (i_lasso INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ DECLARE ans RECORD; trans INT; BEGIN - SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = record; + SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid; FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP RETURN QUERY @@ -501,7 +501,7 @@ BEGIN FROM actor.org_unit_descendants(ans.id) d JOIN asset.copy cp ON (cp.circ_lib = d.id) - JOIN asset.call_number cn ON (cn.record = record AND cn.id = cp.call_number) + JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number) GROUP BY 1,2,6; IF NOT FOUND THEN @@ -514,19 +514,19 @@ BEGIN END; $f$ LANGUAGE PLPGSQL; -CREATE OR REPLACE FUNCTION asset.record_copy_count ( place INT, record BIGINT, staff BOOL) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +CREATE OR REPLACE FUNCTION asset.record_copy_count ( place INT, rid BIGINT, staff BOOL) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ BEGIN IF staff IS TRUE THEN IF place > 0 THEN - RETURN QUERY SELECT * FROM asset.staff_ou_record_copy_count( place, record ); + RETURN QUERY SELECT * FROM asset.staff_ou_record_copy_count( place, rid ); ELSE - RETURN QUERY SELECT * FROM asset.staff_lasso_record_copy_count( -place, record ); + RETURN QUERY SELECT * FROM asset.staff_lasso_record_copy_count( -place, rid ); END IF; ELSE IF place > 0 THEN - RETURN QUERY SELECT * FROM asset.opac_ou_record_copy_count( place, record ); + RETURN QUERY SELECT * FROM asset.opac_ou_record_copy_count( place, rid ); ELSE - RETURN QUERY SELECT * FROM asset.opac_lasso_record_copy_count( -place, record ); + RETURN QUERY SELECT * FROM asset.opac_lasso_record_copy_count( -place, rid ); END IF; END IF; @@ -534,12 +534,12 @@ BEGIN END; $f$ LANGUAGE PLPGSQL; -CREATE OR REPLACE FUNCTION asset.opac_ou_metarecord_copy_count (org INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +CREATE OR REPLACE FUNCTION asset.opac_ou_metarecord_copy_count (org INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ DECLARE ans RECORD; trans INT; BEGIN - SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = record; + SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid; FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP RETURN QUERY @@ -551,7 +551,7 @@ BEGIN trans FROM actor.org_unit_descendants(ans.id) d - JOIN asset.opac_visible_copies av ON (av.record = record AND av.circ_lib = d.id) + JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id) JOIN asset.copy cp ON (cp.id = av.id) JOIN metabib.metarecord_source_map m ON (m.source = av.record) GROUP BY 1,2,6; @@ -566,12 +566,12 @@ BEGIN END; $f$ LANGUAGE PLPGSQL; -CREATE OR REPLACE FUNCTION asset.opac_lasso_metarecord_copy_count (i_lasso INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +CREATE OR REPLACE FUNCTION asset.opac_lasso_metarecord_copy_count (i_lasso INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ DECLARE ans RECORD; trans INT; BEGIN - SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = record; + SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid; FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP RETURN QUERY @@ -583,7 +583,7 @@ BEGIN trans FROM actor.org_unit_descendants(ans.id) d - JOIN asset.opac_visible_copies av ON (av.record = record AND av.circ_lib = d.id) + JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id) JOIN asset.copy cp ON (cp.id = av.id) JOIN metabib.metarecord_source_map m ON (m.source = av.record) GROUP BY 1,2,6; @@ -598,12 +598,12 @@ BEGIN END; $f$ LANGUAGE PLPGSQL; -CREATE OR REPLACE FUNCTION asset.staff_ou_metarecord_copy_count (org INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +CREATE OR REPLACE FUNCTION asset.staff_ou_metarecord_copy_count (org INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ DECLARE ans RECORD; trans INT; BEGIN - SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = record; + SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid; FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP RETURN QUERY @@ -616,7 +616,7 @@ BEGIN FROM actor.org_unit_descendants(ans.id) d JOIN asset.copy cp ON (cp.circ_lib = d.id) - JOIN asset.call_number cn ON (cn.record = record AND cn.id = cp.call_number) + JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number) JOIN metabib.metarecord_source_map m ON (m.source = cn.record) GROUP BY 1,2,6; @@ -630,12 +630,12 @@ BEGIN END; $f$ LANGUAGE PLPGSQL; -CREATE OR REPLACE FUNCTION asset.staff_lasso_metarecord_copy_count (i_lasso INT, record BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +CREATE OR REPLACE FUNCTION asset.staff_lasso_metarecord_copy_count (i_lasso INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ DECLARE ans RECORD; trans INT; BEGIN - SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = record; + SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid; FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP RETURN QUERY @@ -648,7 +648,7 @@ BEGIN FROM actor.org_unit_descendants(ans.id) d JOIN asset.copy cp ON (cp.circ_lib = d.id) - JOIN asset.call_number cn ON (cn.record = record AND cn.id = cp.call_number) + JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number) JOIN metabib.metarecord_source_map m ON (m.source = cn.record) GROUP BY 1,2,6; @@ -662,19 +662,19 @@ BEGIN END; $f$ LANGUAGE PLPGSQL; -CREATE OR REPLACE FUNCTION asset.metarecord_copy_count ( place INT, record BIGINT, staff BOOL) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +CREATE OR REPLACE FUNCTION asset.metarecord_copy_count ( place INT, rid BIGINT, staff BOOL) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ BEGIN IF staff IS TRUE THEN IF place > 0 THEN - RETURN QUERY SELECT * FROM asset.staff_ou_metarecord_copy_count( place, record ); + RETURN QUERY SELECT * FROM asset.staff_ou_metarecord_copy_count( place, rid ); ELSE - RETURN QUERY SELECT * FROM asset.staff_lasso_metarecord_copy_count( -place, record ); + RETURN QUERY SELECT * FROM asset.staff_lasso_metarecord_copy_count( -place, rid ); END IF; ELSE IF place > 0 THEN - RETURN QUERY SELECT * FROM asset.opac_ou_metarecord_copy_count( place, record ); + RETURN QUERY SELECT * FROM asset.opac_ou_metarecord_copy_count( place, rid ); ELSE - RETURN QUERY SELECT * FROM asset.opac_lasso_metarecord_copy_count( -place, record ); + RETURN QUERY SELECT * FROM asset.opac_lasso_metarecord_copy_count( -place, rid ); END IF; END IF; diff --git a/Open-ILS/src/sql/Pg/upgrade/0484.sql.pg-90-compat.sql b/Open-ILS/src/sql/Pg/upgrade/0484.sql.pg-90-compat.sql new file mode 100644 index 0000000000..6e4a6a1f1c --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0484.sql.pg-90-compat.sql @@ -0,0 +1,308 @@ +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0484'); -- miker + +DROP FUNCTION asset.metarecord_copy_count ( INT, BIGINT, BOOL ); +DROP FUNCTION asset.record_copy_count ( INT, BIGINT, BOOL ); + +DROP FUNCTION asset.opac_ou_record_copy_count (INT, BIGINT); +CREATE OR REPLACE FUNCTION asset.opac_ou_record_copy_count (org INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +DECLARE + ans RECORD; + trans INT; +BEGIN + SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid; + + FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP + RETURN QUERY + SELECT ans.depth, + ans.id, + COUNT( av.id ), + SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ), + COUNT( av.id ), + trans + FROM + actor.org_unit_descendants(ans.id) d + JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id) + JOIN asset.copy cp ON (cp.id = av.id) + GROUP BY 1,2,6; + + IF NOT FOUND THEN + RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans; + END IF; + + END LOOP; + + RETURN; +END; +$f$ LANGUAGE PLPGSQL; + +DROP FUNCTION asset.opac_lasso_record_copy_count (INT, BIGINT); +CREATE OR REPLACE FUNCTION asset.opac_lasso_record_copy_count (i_lasso INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +DECLARE + ans RECORD; + trans INT; +BEGIN + SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid; + + FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP + RETURN QUERY + SELECT -1, + ans.id, + COUNT( av.id ), + SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ), + COUNT( av.id ), + trans + FROM + actor.org_unit_descendants(ans.id) d + JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id) + JOIN asset.copy cp ON (cp.id = av.id) + GROUP BY 1,2,6; + + IF NOT FOUND THEN + RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans; + END IF; + + END LOOP; + + RETURN; +END; +$f$ LANGUAGE PLPGSQL; + +DROP FUNCTION asset.staff_ou_record_copy_count (INT, BIGINT); +CREATE OR REPLACE FUNCTION asset.staff_ou_record_copy_count (org INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +DECLARE + ans RECORD; + trans INT; +BEGIN + SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid; + + FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP + RETURN QUERY + SELECT ans.depth, + ans.id, + COUNT( cp.id ), + SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ), + COUNT( cp.id ), + trans + FROM + actor.org_unit_descendants(ans.id) d + JOIN asset.copy cp ON (cp.circ_lib = d.id) + JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number) + GROUP BY 1,2,6; + + IF NOT FOUND THEN + RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans; + END IF; + + END LOOP; + + RETURN; +END; +$f$ LANGUAGE PLPGSQL; + +DROP FUNCTION asset.staff_lasso_record_copy_count (INT, BIGINT); +CREATE OR REPLACE FUNCTION asset.staff_lasso_record_copy_count (i_lasso INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +DECLARE + ans RECORD; + trans INT; +BEGIN + SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid; + + FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP + RETURN QUERY + SELECT -1, + ans.id, + COUNT( cp.id ), + SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ), + COUNT( cp.id ), + trans + FROM + actor.org_unit_descendants(ans.id) d + JOIN asset.copy cp ON (cp.circ_lib = d.id) + JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number) + GROUP BY 1,2,6; + + IF NOT FOUND THEN + RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans; + END IF; + + END LOOP; + + RETURN; +END; +$f$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION asset.record_copy_count ( place INT, rid BIGINT, staff BOOL) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +BEGIN + IF staff IS TRUE THEN + IF place > 0 THEN + RETURN QUERY SELECT * FROM asset.staff_ou_record_copy_count( place, rid ); + ELSE + RETURN QUERY SELECT * FROM asset.staff_lasso_record_copy_count( -place, rid ); + END IF; + ELSE + IF place > 0 THEN + RETURN QUERY SELECT * FROM asset.opac_ou_record_copy_count( place, rid ); + ELSE + RETURN QUERY SELECT * FROM asset.opac_lasso_record_copy_count( -place, rid ); + END IF; + END IF; + + RETURN; +END; +$f$ LANGUAGE PLPGSQL; + +DROP FUNCTION asset.opac_ou_metarecord_copy_count (INT, BIGINT); +CREATE OR REPLACE FUNCTION asset.opac_ou_metarecord_copy_count (org INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +DECLARE + ans RECORD; + trans INT; +BEGIN + SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid; + + FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP + RETURN QUERY + SELECT ans.depth, + ans.id, + COUNT( av.id ), + SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ), + COUNT( av.id ), + trans + FROM + actor.org_unit_descendants(ans.id) d + JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id) + JOIN asset.copy cp ON (cp.id = av.id) + JOIN metabib.metarecord_source_map m ON (m.source = av.record) + GROUP BY 1,2,6; + + IF NOT FOUND THEN + RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans; + END IF; + + END LOOP; + + RETURN; +END; +$f$ LANGUAGE PLPGSQL; + +DROP FUNCTION asset.opac_lasso_metarecord_copy_count (INT, BIGINT); +CREATE OR REPLACE FUNCTION asset.opac_lasso_metarecord_copy_count (i_lasso INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +DECLARE + ans RECORD; + trans INT; +BEGIN + SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid; + + FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP + RETURN QUERY + SELECT -1, + ans.id, + COUNT( av.id ), + SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ), + COUNT( av.id ), + trans + FROM + actor.org_unit_descendants(ans.id) d + JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id) + JOIN asset.copy cp ON (cp.id = av.id) + JOIN metabib.metarecord_source_map m ON (m.source = av.record) + GROUP BY 1,2,6; + + IF NOT FOUND THEN + RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans; + END IF; + + END LOOP; + + RETURN; +END; +$f$ LANGUAGE PLPGSQL; + +DROP FUNCTION asset.staff_ou_metarecord_copy_count (INT, BIGINT); +CREATE OR REPLACE FUNCTION asset.staff_ou_metarecord_copy_count (org INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +DECLARE + ans RECORD; + trans INT; +BEGIN + SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid; + + FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP + RETURN QUERY + SELECT ans.depth, + ans.id, + COUNT( cp.id ), + SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ), + COUNT( cp.id ), + trans + FROM + actor.org_unit_descendants(ans.id) d + JOIN asset.copy cp ON (cp.circ_lib = d.id) + JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number) + JOIN metabib.metarecord_source_map m ON (m.source = cn.record) + GROUP BY 1,2,6; + + IF NOT FOUND THEN + RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans; + END IF; + + END LOOP; + + RETURN; +END; +$f$ LANGUAGE PLPGSQL; + +DROP FUNCTION asset.staff_lasso_metarecord_copy_count (INT, BIGINT); +CREATE OR REPLACE FUNCTION asset.staff_lasso_metarecord_copy_count (i_lasso INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +DECLARE + ans RECORD; + trans INT; +BEGIN + SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid; + + FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP + RETURN QUERY + SELECT -1, + ans.id, + COUNT( cp.id ), + SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ), + COUNT( cp.id ), + trans + FROM + actor.org_unit_descendants(ans.id) d + JOIN asset.copy cp ON (cp.circ_lib = d.id) + JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number) + JOIN metabib.metarecord_source_map m ON (m.source = cn.record) + GROUP BY 1,2,6; + + IF NOT FOUND THEN + RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans; + END IF; + + END LOOP; + + RETURN; +END; +$f$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION asset.metarecord_copy_count ( place INT, rid BIGINT, staff BOOL) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$ +BEGIN + IF staff IS TRUE THEN + IF place > 0 THEN + RETURN QUERY SELECT * FROM asset.staff_ou_metarecord_copy_count( place, rid ); + ELSE + RETURN QUERY SELECT * FROM asset.staff_lasso_metarecord_copy_count( -place, rid ); + END IF; + ELSE + IF place > 0 THEN + RETURN QUERY SELECT * FROM asset.opac_ou_metarecord_copy_count( place, rid ); + ELSE + RETURN QUERY SELECT * FROM asset.opac_lasso_metarecord_copy_count( -place, rid ); + END IF; + END IF; + + RETURN; +END; +$f$ LANGUAGE PLPGSQL; + +COMMIT; -- 2.43.2