Changed the way authority tags were being pulled to specifically use an aggregate...

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 <stevecallender@esilibrary.com>
Signed-off-by: Bill Erickson <berick@esilibrary.com>

diff --git a/Open-ILS/src/sql/Pg/011.schema.authority.sql b/Open-ILS/src/sql/Pg/011.schema.authority.sql
index 10ac1d6..2e4f14a 100644 (file)
--- 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 (file)
index 0000000..5a2dcc9
--- /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;