From bd50424f525f4a32ccc965b3835fee7256015254 Mon Sep 17 00:00:00 2001 From: Dan Scott Date: Tue, 11 Oct 2011 23:53:33 -0400 Subject: [PATCH] Replace ARRAY_ACCUM() custom function with ARRAY_AGG() Tested each replacement to ensure that the return of NULL instead of '{}' empty array doesn't break anything - although some functions didn't seem to work with either function (for example, asset.merge_record_assets() doesn't seem to actually move an 856 from the source to the target record). Also replace ARRAY_TO_STRING(ARRAY_AGG()) with STRING_AGG() in a couple of places; see http://www.depesz.com/index.php/2010/02/17/waiting-for-9-0-string_agg/ for one reason why (likely performance gain). Signed-off-by: Dan Scott Signed-off-by: Ben Shum --- Open-ILS/src/sql/Pg/000.functions.general.sql | 2 +- Open-ILS/src/sql/Pg/030.schema.metabib.sql | 4 ++-- Open-ILS/src/sql/Pg/100.circ_matrix.sql | 6 +++--- Open-ILS/src/sql/Pg/110.hold_matrix.sql | 2 +- Open-ILS/src/sql/Pg/300.schema.staged_search.sql | 12 ++++++------ Open-ILS/src/sql/Pg/990.schema.unapi.sql | 2 +- Open-ILS/src/sql/Pg/999.functions.global.sql | 2 +- Open-ILS/src/sql/Pg/example.reporter-extension.sql | 6 +++--- Open-ILS/src/sql/Pg/reporter-schema.sql | 12 ++++++------ 9 files changed, 24 insertions(+), 24 deletions(-) diff --git a/Open-ILS/src/sql/Pg/000.functions.general.sql b/Open-ILS/src/sql/Pg/000.functions.general.sql index 51ce2b7764..d62bd70c76 100644 --- a/Open-ILS/src/sql/Pg/000.functions.general.sql +++ b/Open-ILS/src/sql/Pg/000.functions.general.sql @@ -15,7 +15,7 @@ $$ LANGUAGE plpgsql; SELECT evergreen.change_db_setting('search_path', ARRAY['evergreen','public','pg_catalog']); -CREATE OR REPLACE FUNCTION evergreen.array_remove_item_by_value(inp ANYARRAY, el ANYELEMENT) RETURNS anyarray AS $$ SELECT ARRAY_ACCUM(x.e) FROM UNNEST( $1 ) x(e) WHERE x.e <> $2; $$ LANGUAGE SQL; +CREATE OR REPLACE FUNCTION evergreen.array_remove_item_by_value(inp ANYARRAY, el ANYELEMENT) RETURNS anyarray AS $$ SELECT ARRAY_AGG(x.e) FROM UNNEST( $1 ) x(e) WHERE x.e <> $2; $$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION evergreen.lowercase( TEXT ) RETURNS TEXT AS $$ return lc(shift); diff --git a/Open-ILS/src/sql/Pg/030.schema.metabib.sql b/Open-ILS/src/sql/Pg/030.schema.metabib.sql index 83200e287a..10fc2de619 100644 --- a/Open-ILS/src/sql/Pg/030.schema.metabib.sql +++ b/Open-ILS/src/sql/Pg/030.schema.metabib.sql @@ -1112,7 +1112,7 @@ BEGIN CONTINUE WHEN uri_href IS NULL; -- Get the distinct list of libraries wanting to use - SELECT ARRAY_ACCUM( + SELECT ARRAY_AGG( DISTINCT REGEXP_REPLACE( x, $re$^.*?\((\w+)\).*$$re$, @@ -1304,7 +1304,7 @@ BEGIN FOR attr_def IN SELECT * FROM config.record_attr_definition ORDER BY format LOOP IF attr_def.tag IS NOT NULL THEN -- tag (and optional subfield list) selection - SELECT ARRAY_TO_STRING(ARRAY_ACCUM(value), COALESCE(attr_def.joiner,' ')) INTO attr_value + SELECT ARRAY_TO_STRING(ARRAY_AGG(value), COALESCE(attr_def.joiner,' ')) INTO attr_value FROM (SELECT * FROM metabib.full_rec ORDER BY tag, subfield) AS x WHERE record = NEW.id AND tag LIKE attr_def.tag diff --git a/Open-ILS/src/sql/Pg/100.circ_matrix.sql b/Open-ILS/src/sql/Pg/100.circ_matrix.sql index ef829dbc19..837c34e534 100644 --- a/Open-ILS/src/sql/Pg/100.circ_matrix.sql +++ b/Open-ILS/src/sql/Pg/100.circ_matrix.sql @@ -703,7 +703,7 @@ BEGIN AND (stop_date IS NULL or stop_date > NOW()) AND standing_penalty = 1; - SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( max_fines.org_unit ); + SELECT INTO context_org_list ARRAY_AGG(id) FROM actor.org_unit_full_path( max_fines.org_unit ); SELECT SUM(f.balance_owed) INTO current_fines FROM money.materialized_billable_xact_summary f @@ -1024,7 +1024,7 @@ BEGIN AND (stop_date IS NULL or stop_date > NOW()) AND standing_penalty = 4; - SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( max_fines.org_unit ); + SELECT INTO context_org_list ARRAY_AGG(id) FROM actor.org_unit_full_path( max_fines.org_unit ); SELECT SUM(f.balance_owed) INTO current_fines FROM money.materialized_billable_xact_summary f @@ -1087,7 +1087,7 @@ BEGIN IF max_fines.threshold IS NOT NULL THEN - SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( max_fines.org_unit ); + SELECT INTO context_org_list ARRAY_AGG(id) FROM actor.org_unit_full_path( max_fines.org_unit ); -- first, see if the user had paid down to the threshold SELECT SUM(f.balance_owed) INTO current_fines diff --git a/Open-ILS/src/sql/Pg/110.hold_matrix.sql b/Open-ILS/src/sql/Pg/110.hold_matrix.sql index be2ff6511d..25ac26491d 100644 --- a/Open-ILS/src/sql/Pg/110.hold_matrix.sql +++ b/Open-ILS/src/sql/Pg/110.hold_matrix.sql @@ -239,7 +239,7 @@ DECLARE hold_penalty TEXT; BEGIN SELECT INTO user_object * FROM actor.usr WHERE id = match_user; - SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( pickup_ou ); + SELECT INTO context_org_list ARRAY_AGG(id) FROM actor.org_unit_full_path( pickup_ou ); result.success := TRUE; diff --git a/Open-ILS/src/sql/Pg/300.schema.staged_search.sql b/Open-ILS/src/sql/Pg/300.schema.staged_search.sql index 6cd9918451..477d5d08c8 100644 --- a/Open-ILS/src/sql/Pg/300.schema.staged_search.sql +++ b/Open-ILS/src/sql/Pg/300.schema.staged_search.sql @@ -80,22 +80,22 @@ BEGIN IF param_search_ou > 0 THEN IF param_depth IS NOT NULL THEN - SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou, param_depth ); + SELECT ARRAY_AGG(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou, param_depth ); ELSE - SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou ); + SELECT ARRAY_AGG(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou ); END IF; - SELECT array_accum(distinct id) INTO luri_org_list FROM actor.org_unit_ancestors( param_search_ou ); + SELECT ARRAY_AGG(distinct id) INTO luri_org_list FROM actor.org_unit_ancestors( param_search_ou ); ELSIF param_search_ou < 0 THEN - SELECT array_accum(distinct org_unit) INTO search_org_list FROM actor.org_lasso_map WHERE lasso = -param_search_ou; + SELECT ARRAY_AGG(distinct org_unit) INTO search_org_list FROM actor.org_lasso_map WHERE lasso = -param_search_ou; FOR tmp_int IN SELECT * FROM UNNEST(search_org_list) LOOP - SELECT array_accum(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors( tmp_int ); + SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors( tmp_int ); luri_org_list := luri_org_list || tmp_int_list; END LOOP; - SELECT array_accum(DISTINCT x.id) INTO luri_org_list FROM UNNEST(luri_org_list) x(id); + SELECT ARRAY_AGG(DISTINCT x.id) INTO luri_org_list FROM UNNEST(luri_org_list) x(id); ELSIF param_search_ou = 0 THEN -- reserved for user lassos (ou_buckets/type='lasso') with ID passed in depth ... hack? sure. diff --git a/Open-ILS/src/sql/Pg/990.schema.unapi.sql b/Open-ILS/src/sql/Pg/990.schema.unapi.sql index c7bc8bba36..f819c642be 100644 --- a/Open-ILS/src/sql/Pg/990.schema.unapi.sql +++ b/Open-ILS/src/sql/Pg/990.schema.unapi.sql @@ -11,7 +11,7 @@ ROWS 1; CREATE OR REPLACE FUNCTION evergreen.array_remove_item_by_value(inp ANYARRAY, el ANYELEMENT) RETURNS anyarray AS $$ - SELECT ARRAY_ACCUM(x.e) FROM UNNEST( $1 ) x(e) WHERE x.e <> $2; + SELECT ARRAY_AGG(x.e) FROM UNNEST( $1 ) x(e) WHERE x.e <> $2; $$ LANGUAGE SQL STABLE; CREATE OR REPLACE FUNCTION evergreen.rank_ou(lib INT, search_lib INT, pref_lib INT DEFAULT NULL) diff --git a/Open-ILS/src/sql/Pg/999.functions.global.sql b/Open-ILS/src/sql/Pg/999.functions.global.sql index e57158d9f9..070c58314f 100644 --- a/Open-ILS/src/sql/Pg/999.functions.global.sql +++ b/Open-ILS/src/sql/Pg/999.functions.global.sql @@ -1004,7 +1004,7 @@ BEGIN ' ind1="' || FIRST(ind1) || '"' || ' ind2="' || FIRST(ind2) || '">' || array_to_string( - array_accum( + ARRAY_AGG( '' || regexp_replace( regexp_replace( diff --git a/Open-ILS/src/sql/Pg/example.reporter-extension.sql b/Open-ILS/src/sql/Pg/example.reporter-extension.sql index aaf2b239ec..5095aeecf6 100644 --- a/Open-ILS/src/sql/Pg/example.reporter-extension.sql +++ b/Open-ILS/src/sql/Pg/example.reporter-extension.sql @@ -256,7 +256,7 @@ CREATE OR REPLACE VIEW money.open_circ_balance_by_owning_lib AS CREATE OR REPLACE VIEW money.open_balance_by_owning_lib AS SELECT owning_lib, - ARRAY_TO_STRING(ARRAY_ACCUM(DISTINCT billing_type), ', ') AS billing_types, + STRING_AGG(DISTINCT billing_type, ', ') AS billing_types, SUM(billed) - SUM( COALESCE((SELECT SUM(amount) AS paid FROM money.payment WHERE NOT voided AND xact = x.id), 0::NUMERIC) ) AS balance FROM money.open_circ_balance_by_owning_lib x GROUP BY 1; @@ -283,7 +283,7 @@ CREATE OR REPLACE VIEW money.open_circ_balance_by_circ_and_owning_lib AS CREATE OR REPLACE VIEW money.open_balance_by_circ_and_owning_lib AS SELECT circ_lib, owning_lib, - ARRAY_TO_STRING(ARRAY_ACCUM(DISTINCT billing_type), ', ') AS billing_types, + STRING_AGG(DISTINCT billing_type, ', ') AS billing_types, SUM(billed) - SUM( COALESCE((SELECT SUM(amount) AS paid FROM money.payment WHERE NOT voided AND xact = x.id), 0::NUMERIC) ) AS balance FROM money.open_circ_balance_by_circ_and_owning_lib x GROUP BY 1,2; @@ -311,7 +311,7 @@ CREATE OR REPLACE VIEW money.open_circ_balance_by_usr_home_and_owning_lib AS CREATE OR REPLACE VIEW money.open_balance_by_usr_home_and_owning_lib AS SELECT home_ou, owning_lib, - ARRAY_TO_STRING(ARRAY_ACCUM(DISTINCT billing_type), ', ') AS billing_types, + STRING_AGG(DISTINCT billing_type, ', ') AS billing_types, SUM(billed) - SUM( COALESCE((SELECT SUM(amount) AS paid FROM money.payment WHERE NOT voided AND xact = x.id), 0::NUMERIC) ) AS balance FROM money.open_circ_balance_by_usr_home_and_owning_lib x GROUP BY 1,2; diff --git a/Open-ILS/src/sql/Pg/reporter-schema.sql b/Open-ILS/src/sql/Pg/reporter-schema.sql index bad6892f2d..70e24dad87 100644 --- a/Open-ILS/src/sql/Pg/reporter-schema.sql +++ b/Open-ILS/src/sql/Pg/reporter-schema.sql @@ -126,8 +126,8 @@ SELECT r.id, series_title.value AS series_title, series_statement.value AS series_statement, summary.value AS summary, - ARRAY_ACCUM( DISTINCT REPLACE(SUBSTRING(isbn.value FROM $$^\S+$$), '-', '') ) AS isbn, - ARRAY_ACCUM( DISTINCT REGEXP_REPLACE(issn.value, E'^\\S*(\\d{4})[-\\s](\\d{3,4}x?)', E'\\1 \\2') ) AS issn, + ARRAY_AGG( DISTINCT REPLACE(SUBSTRING(isbn.value FROM $$^\S+$$), '-', '') ) AS isbn, + ARRAY_AGG( DISTINCT REGEXP_REPLACE(issn.value, E'^\\S*(\\d{4})[-\\s](\\d{3,4}x?)', E'\\1 \\2') ) AS issn, ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '650' AND subfield = 'a' AND record = r.id)) AS topic_subject, ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '651' AND subfield = 'a' AND record = r.id)) AS geographic_subject, ARRAY((SELECT DISTINCT value FROM metabib.full_rec WHERE tag = '655' AND subfield = 'a' AND record = r.id)) AS genre, @@ -156,10 +156,10 @@ SELECT r.id, r.tcn_value, FIRST(title.value) AS title, FIRST(author.value) AS author, - ARRAY_TO_STRING(ARRAY_ACCUM( DISTINCT publisher.value), ', ') AS publisher, - ARRAY_TO_STRING(ARRAY_ACCUM( DISTINCT SUBSTRING(pubdate.value FROM $$\d+$$) ), ', ') AS pubdate, - ARRAY_ACCUM( DISTINCT REPLACE(SUBSTRING(isbn.value FROM $$^\S+$$), '-', '') ) AS isbn, - ARRAY_ACCUM( DISTINCT REGEXP_REPLACE(issn.value, E'^\\S*(\\d{4})[-\\s](\\d{3,4}x?)', E'\\1 \\2') ) AS issn + STRING_AGG(DISTINCT publisher.value, ', ') AS publisher, + STRING_AGG(DISTINCT SUBSTRING(pubdate.value FROM $$\d+$$), ', ') AS pubdate, + ARRAY_AGG( DISTINCT REPLACE(SUBSTRING(isbn.value FROM $$^\S+$$), '-', '') ) AS isbn, + ARRAY_AGG( DISTINCT REGEXP_REPLACE(issn.value, E'^\\S*(\\d{4})[-\\s](\\d{3,4}x?)', E'\\1 \\2') ) AS issn FROM biblio.record_entry r LEFT JOIN metabib.full_rec title ON (r.id = title.record AND title.tag = '245' AND title.subfield = 'a') LEFT JOIN metabib.full_rec author ON (r.id = author.record AND author.tag IN ('100','110','111') AND author.subfield = 'a') -- 2.43.2