From 22cf746ef809470e630e98b75ac1021c3925e52f Mon Sep 17 00:00:00 2001 From: Galen Charlton Date: Thu, 3 Mar 2016 08:57:27 -0500 Subject: [PATCH] LP#1505286: use older syntax for Pg 9.2+ This makes no function difference, but potentially might remove a small speed bump for folks doing detailed schema diffs. Signed-off-by: Galen Charlton --- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- .../0970.schema.limit_facets_pg91_compat.sql | 43 ++++++++++++++++++++++ 2 files changed, 44 insertions(+), 1 deletion(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0970.schema.limit_facets_pg91_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 96263afeac..915ffbe5a6 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -91,7 +91,7 @@ CREATE TRIGGER no_overlapping_deps BEFORE INSERT OR UPDATE ON config.db_patch_dependencies FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('deprecates'); -INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0969', :eg_version); -- jeffdavis/stompro +INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0970', :eg_version); -- Dyrcona/gmcharlt CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/upgrade/0970.schema.limit_facets_pg91_compat.sql b/Open-ILS/src/sql/Pg/upgrade/0970.schema.limit_facets_pg91_compat.sql new file mode 100644 index 0000000000..26dc8b4f2b --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0970.schema.limit_facets_pg91_compat.sql @@ -0,0 +1,43 @@ +BEGIN; + +SELECT evergreen.upgrade_deps_block_check('0970', :eg_version); -- Dyrcona/gmcharlt + +CREATE OR REPLACE FUNCTION search.facets_for_record_set(ignore_facet_classes TEXT[], hits BIGINT[]) RETURNS TABLE (id INT, value TEXT, count BIGINT) AS $$ + SELECT id, value, count FROM ( + SELECT mfae.field AS id, + mfae.value, + COUNT(DISTINCT mmrsm.source), + row_number() OVER ( + PARTITION BY mfae.field ORDER BY COUNT(distinct mmrsm.source) DESC + ) AS rownum + FROM metabib.facet_entry mfae + JOIN metabib.metarecord_source_map mmrsm ON (mfae.source = mmrsm.source) + JOIN config.metabib_field cmf ON (cmf.id = mfae.field) + WHERE mmrsm.source IN (SELECT * FROM unnest($2)) + AND cmf.facet_field + AND cmf.field_class NOT IN (SELECT * FROM unnest($1)) + GROUP by 1, 2 + ) all_facets + WHERE rownum <= (SELECT COALESCE((SELECT value::INT FROM config.global_flag WHERE name = 'search.max_facets_per_field' AND enabled), 1000)); +$$ LANGUAGE SQL; + +CREATE OR REPLACE FUNCTION search.facets_for_metarecord_set(ignore_facet_classes TEXT[], hits BIGINT[]) RETURNS TABLE (id INT, value TEXT, count BIGINT) AS $$ + SELECT id, value, count FROM ( + SELECT mfae.field AS id, + mfae.value, + COUNT(DISTINCT mmrsm.metarecord), + row_number() OVER ( + PARTITION BY mfae.field ORDER BY COUNT(distinct mmrsm.metarecord) DESC + ) AS rownum + FROM metabib.facet_entry mfae + JOIN metabib.metarecord_source_map mmrsm ON (mfae.source = mmrsm.source) + JOIN config.metabib_field cmf ON (cmf.id = mfae.field) + WHERE mmrsm.metarecord IN (SELECT * FROM unnest($2)) + AND cmf.facet_field + AND cmf.field_class NOT IN (SELECT * FROM unnest($1)) + GROUP by 1, 2 + ) all_facets + WHERE rownum <= (SELECT COALESCE((SELECT value::INT FROM config.global_flag WHERE name = 'search.max_facets_per_field' AND enabled), 1000)); +$$ LANGUAGE SQL; + +COMMIT; -- 2.11.0