From e2e878a8e9c7eef0b7707e8f4e0b47c329b9e458 Mon Sep 17 00:00:00 2001 From: Steven Callender Date: Thu, 7 Mar 2013 09:19:22 -0500 Subject: [PATCH] Added aggregate changes to atag and btag authoritie functions as well. Signed-off-by: Steven Callender --- Open-ILS/src/sql/Pg/011.schema.authority.sql | 27 +++++++++++----------- ...XXX.function.axis_authority_tags_refs_aggregate | 21 +++++++++++++++++ 2 files changed, 35 insertions(+), 13 deletions(-) diff --git a/Open-ILS/src/sql/Pg/011.schema.authority.sql b/Open-ILS/src/sql/Pg/011.schema.authority.sql index 0e3dd8d3c0..2e4f14abd2 100644 --- a/Open-ILS/src/sql/Pg/011.schema.authority.sql +++ b/Open-ILS/src/sql/Pg/011.schema.authority.sql @@ -616,7 +616,7 @@ CREATE OR REPLACE FUNCTION authority.axis_authority_tags_refs(a TEXT) RETURNS IN (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; + WHERE axis = $1) x $$ LANGUAGE SQL; @@ -624,32 +624,33 @@ CREATE OR REPLACE FUNCTION authority.btag_authority_tags(btag TEXT) RETURNS INT[ 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 index b6a72d2bff..5a2dcc9900 100644 --- 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 @@ -15,4 +15,25 @@ CREATE OR REPLACE FUNCTION authority.axis_authority_tags_refs(a TEXT) RETURNS IN 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.11.0