From 2cd7d4866f5bc960b46a1486247fb1b3b40cee47 Mon Sep 17 00:00:00 2001 From: Steven Callender Date: Thu, 7 Mar 2013 09:28:08 -0500 Subject: [PATCH] Changed the way authority tags were being pulled to specifically use an aggregate array. There appears to have been a change in postgres at some point between 9.0 and 9.1 to the aggregate method. Because of this, postgres was not returning the proper results when pulling tags for authorities. This change will force postgres to do a proper aggregate array call and return the correct results. Signed-off-by: Steven Callender Signed-off-by: Bill Erickson --- Open-ILS/src/sql/Pg/011.schema.authority.sql | 40 ++++++++++--------- ...unction.axis_authority_tags_refs_aggregate | 39 ++++++++++++++++++ 2 files changed, 60 insertions(+), 19 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.function.axis_authority_tags_refs_aggregate diff --git a/Open-ILS/src/sql/Pg/011.schema.authority.sql b/Open-ILS/src/sql/Pg/011.schema.authority.sql index 10ac1d6206..2e4f14abd2 100644 --- a/Open-ILS/src/sql/Pg/011.schema.authority.sql +++ b/Open-ILS/src/sql/Pg/011.schema.authority.sql @@ -608,47 +608,49 @@ CREATE OR REPLACE FUNCTION authority.axis_authority_tags(a TEXT) RETURNS INT[] A SELECT ARRAY_ACCUM(field) FROM authority.browse_axis_authority_field_map WHERE axis = $1; $$ LANGUAGE SQL; + CREATE OR REPLACE FUNCTION authority.axis_authority_tags_refs(a TEXT) RETURNS INT[] AS $$ - SELECT ARRAY_CAT( - ARRAY[a.field], - (SELECT ARRAY_ACCUM(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.field) - ) - FROM authority.browse_axis_authority_field_map a - WHERE axis = $1 + SELECT ARRAY_AGG(y) from ( + SELECT unnest(ARRAY_CAT( + ARRAY[a.field], + (SELECT ARRAY_ACCUM(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.field) + )) y + FROM authority.browse_axis_authority_field_map a + WHERE axis = $1) x $$ LANGUAGE SQL; - CREATE OR REPLACE FUNCTION authority.btag_authority_tags(btag TEXT) RETURNS INT[] AS $$ SELECT ARRAY_ACCUM(authority_field) FROM authority.control_set_bib_field WHERE tag = $1 $$ LANGUAGE SQL; + CREATE OR REPLACE FUNCTION authority.btag_authority_tags_refs(btag TEXT) RETURNS INT[] AS $$ - SELECT ARRAY_CAT( - ARRAY[a.authority_field], - (SELECT ARRAY_ACCUM(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.authority_field) - ) + SELECT ARRAY_AGG(y) from ( + SELECT unnest(ARRAY_CAT( + ARRAY[a.authority_field], + (SELECT ARRAY_ACCUM(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.authority_field) + )) y FROM authority.control_set_bib_field a - WHERE a.tag = $1 + WHERE a.tag = $1) x $$ LANGUAGE SQL; - CREATE OR REPLACE FUNCTION authority.atag_authority_tags(atag TEXT) RETURNS INT[] AS $$ SELECT ARRAY_ACCUM(id) FROM authority.control_set_authority_field WHERE tag = $1 $$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION authority.atag_authority_tags_refs(atag TEXT) RETURNS INT[] AS $$ - SELECT ARRAY_CAT( - ARRAY[a.id], - (SELECT ARRAY_ACCUM(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.id) - ) + SELECT ARRAY_AGG(y) from ( + SELECT unnest(ARRAY_CAT( + ARRAY[a.id], + (SELECT ARRAY_ACCUM(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.id) + )) y FROM authority.control_set_authority_field a - WHERE a.tag = $1 + WHERE a.tag = $1) x $$ LANGUAGE SQL; - CREATE OR REPLACE FUNCTION authority.axis_browse_center( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$ SELECT * FROM authority.simple_heading_browse_center(authority.axis_authority_tags($1), $2, $3, $4) $$ LANGUAGE SQL ROWS 10; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.function.axis_authority_tags_refs_aggregate b/Open-ILS/src/sql/Pg/upgrade/XXXX.function.axis_authority_tags_refs_aggregate new file mode 100644 index 0000000000..5a2dcc9900 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.function.axis_authority_tags_refs_aggregate @@ -0,0 +1,39 @@ +-- Evergreen DB patch XXXX.function.axis_authority_tags_refs_aggregate.sql +-- +BEGIN; + +-- check whether patch can be applied +SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +CREATE OR REPLACE FUNCTION authority.axis_authority_tags_refs(a TEXT) RETURNS INT[] AS $$ + SELECT ARRAY_AGG(y) from ( + SELECT unnest(ARRAY_CAT( + ARRAY[a.field], + (SELECT ARRAY_ACCUM(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.field) + )) y + FROM authority.browse_axis_authority_field_map a + WHERE axis = $1) x; +$$ LANGUAGE SQL; + +CREATE OR REPLACE FUNCTION authority.btag_authority_tags_refs(btag TEXT) RETURNS INT[] AS $$ + SELECT ARRAY_AGG(y) from ( + SELECT unnest(ARRAY_CAT( + ARRAY[a.authority_field], + (SELECT ARRAY_ACCUM(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.authority_field) + )) y + FROM authority.control_set_bib_field a + WHERE a.tag = $1) x +$$ LANGUAGE SQL; + +CREATE OR REPLACE FUNCTION authority.atag_authority_tags_refs(atag TEXT) RETURNS INT[] AS $$ + SELECT ARRAY_AGG(y) from ( + SELECT unnest(ARRAY_CAT( + ARRAY[a.id], + (SELECT ARRAY_ACCUM(x.id) FROM authority.control_set_authority_field x WHERE x.main_entry = a.id) + )) y + FROM authority.control_set_authority_field a + WHERE a.tag = $1) x +$$ LANGUAGE SQL; + + +COMMIT; -- 2.43.2