From 0eae7af475fdbb974f7c43128dc9fda68f1ec5c5 Mon Sep 17 00:00:00 2001 From: Dan Scott Date: Sat, 28 May 2011 23:51:49 -0400 Subject: [PATCH] LP#789747 - Use native functions instead of SQL functions There are two functions native to PostgreSQL since at least 8.4 for which Evergreen is currently using custom SQL functions: * array_agg() - array_accum() * unnest() - search.explode_array() and public.explode_array() Swap'em out across the board. Signed-off-by: Dan Scott --- Open-ILS/examples/build-eg-replication.sh | 4 +- Open-ILS/examples/fm_IDL.xml | 10 ++-- .../perlmods/lib/OpenILS/Application/Booking.pm | 4 +- .../Application/Storage/Driver/Pg/QueryParser.pm | 2 +- Open-ILS/src/sql/Pg/002.functions.config.sql | 2 +- Open-ILS/src/sql/Pg/030.schema.metabib.sql | 10 ++-- Open-ILS/src/sql/Pg/100.circ_matrix.sql | 8 ++-- Open-ILS/src/sql/Pg/110.hold_matrix.sql | 6 +-- Open-ILS/src/sql/Pg/300.schema.staged_search.sql | 56 +++++++++++----------- 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 +-- .../sql/Pg/pgmemcache-ou_tree_traversal_cache.sql | 2 +- Open-ILS/src/sql/Pg/reporter-schema.sql | 12 ++--- 14 files changed, 63 insertions(+), 63 deletions(-) diff --git a/Open-ILS/examples/build-eg-replication.sh b/Open-ILS/examples/build-eg-replication.sh index 83a1134001..0a52252350 100755 --- a/Open-ILS/examples/build-eg-replication.sh +++ b/Open-ILS/examples/build-eg-replication.sh @@ -52,7 +52,7 @@ fi if [ "_$TABLES" == "_" ]; then TABLES=$(psql -tc " - select array_to_string(array_accum(table_schema || '.' || table_name),' ') + select array_to_string(array_gg(table_schema || '.' || table_name),' ') from information_schema.tables where table_schema in ( 'acq', 'action', 'action_trigger', 'actor', 'asset', 'asset_hist', 'auditor', @@ -65,7 +65,7 @@ if [ "_$TABLES" == "_" ]; then fi if [ "_$SEQUENCES" == "_" ]; then - SEQUENCES=$(psql -tc "select array_to_string(array_accum(schemaname || '.' || relname),' ') from pg_statio_user_sequences;") + SEQUENCES=$(psql -tc "select array_to_string(array_agg(schemaname || '.' || relname),' ') from pg_statio_user_sequences;") fi diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index 724e8f55a0..1f14cb39fe 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -2881,7 +2881,7 @@ SELECT usr, SELECT usr, - ARRAY_TO_STRING(ARRAY_ACCUM( + ARRAY_TO_STRING(array_agg( CASE WHEN ( ((fine_interval >= '1 day' AND due_date >= 'today') OR (fine_interval < '1 day' AND due_date > 'now')) @@ -2891,7 +2891,7 @@ SELECT usr, END ),',') AS out, - ARRAY_TO_STRING(ARRAY_ACCUM( + ARRAY_TO_STRING(array_agg( CASE WHEN ( ((fine_interval >= '1 day' AND due_date < 'today') OR (fine_interval < '1 day' AND due_date < 'now')) @@ -2901,9 +2901,9 @@ SELECT usr, END ),',') AS overdue, - ARRAY_TO_STRING(ARRAY_ACCUM( CASE WHEN (xact_finish IS NULL AND stop_fines = 'LOST') THEN id ELSE 0 END),',') AS lost, - ARRAY_TO_STRING(ARRAY_ACCUM( CASE WHEN (xact_finish IS NULL AND stop_fines = 'CLAIMSRETURNED') THEN id ELSE 0 END),',') AS claims_returned, - ARRAY_TO_STRING(ARRAY_ACCUM( CASE WHEN (xact_finish IS NULL AND stop_fines = 'LONGOVERDUE') THEN id ELSE 0 END),',') AS long_overdue + ARRAY_TO_STRING(array_agg( CASE WHEN (xact_finish IS NULL AND stop_fines = 'LOST') THEN id ELSE 0 END),',') AS lost, + ARRAY_TO_STRING(array_agg( CASE WHEN (xact_finish IS NULL AND stop_fines = 'CLAIMSRETURNED') THEN id ELSE 0 END),',') AS claims_returned, + ARRAY_TO_STRING(array_agg( CASE WHEN (xact_finish IS NULL AND stop_fines = 'LONGOVERDUE') THEN id ELSE 0 END),',') AS long_overdue FROM action.circulation WHERE checkin_time IS NULL GROUP BY 1 diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Booking.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Booking.pm index 5e75fa840d..3e4383a004 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Booking.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Booking.pm @@ -347,7 +347,7 @@ sub resource_list_by_attrs { if (!ref($filters->{attribute_values})); $query->{having}->{'+bram'}->{value}->{'@>'} = { - transform => 'array_accum', + transform => 'array_agg', value => '$_' . $$ . '${' . join(',', @{$filters->{attribute_values}}) . '}$_' . $$ . '$' @@ -542,7 +542,7 @@ sub reservation_list_by_filters { if (!ref($filters->{attribute_values})); $query->{having}->{'+bravm'}->{attr_value}->{'@>'} = { - transform => 'array_accum', + transform => 'array_agg', value => '$_' . $$ . '${' . join(',', @{$filters->{attribute_values}}) . '}$_' . $$ . '$' diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm index b858fd55bd..36960f2739 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm @@ -594,7 +594,7 @@ sub toSQL { my $sql = <'date1') AS tie_break diff --git a/Open-ILS/src/sql/Pg/002.functions.config.sql b/Open-ILS/src/sql/Pg/002.functions.config.sql index c97a1dd579..56a3df86c2 100644 --- a/Open-ILS/src/sql/Pg/002.functions.config.sql +++ b/Open-ILS/src/sql/Pg/002.functions.config.sql @@ -278,7 +278,7 @@ BEGIN select_list := ARRAY_APPEND( select_list, $sel$ - EXPLODE_ARRAY( + unnest( COALESCE( NULLIF( oils_xpath( diff --git a/Open-ILS/src/sql/Pg/030.schema.metabib.sql b/Open-ILS/src/sql/Pg/030.schema.metabib.sql index 5dc4761691..98f960574b 100644 --- a/Open-ILS/src/sql/Pg/030.schema.metabib.sql +++ b/Open-ILS/src/sql/Pg/030.schema.metabib.sql @@ -303,7 +303,7 @@ BEGIN xml_node_list := oils_xpath( idx.xpath, transformed_xml, ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]] ); raw_text := NULL; - FOR xml_node IN SELECT x FROM explode_array(xml_node_list) AS x LOOP + FOR xml_node IN SELECT x FROM unnest(xml_node_list) AS x LOOP CONTINUE WHEN xml_node !~ E'^\\s*<'; curr_text := ARRAY_TO_STRING( @@ -858,7 +858,7 @@ BEGIN CONTINUE WHEN uri_href IS NULL OR uri_label IS NULL; -- Get the distinct list of libraries wanting to use - SELECT ARRAY_ACCUM( + SELECT array_agg( DISTINCT REGEXP_REPLACE( x, $re$^.*?\((\w+)\).*$$re$, @@ -961,7 +961,7 @@ BEGIN INSERT INTO metabib.metarecord_source_map (metarecord, source) VALUES (old_mr, bib_id); -- new source mapping IF ARRAY_UPPER(deleted_mrs,1) > 0 THEN - UPDATE action.hold_request SET target = old_mr WHERE target IN ( SELECT explode_array(deleted_mrs) ) AND hold_type = 'M'; -- if we had to delete any MRs above, make sure their holds are moved + UPDATE action.hold_request SET target = old_mr WHERE target IN ( SELECT unnest(deleted_mrs) ) AND hold_type = 'M'; -- if we had to delete any MRs above, make sure their holds are moved END IF; RETURN old_mr; @@ -976,7 +976,7 @@ CREATE OR REPLACE FUNCTION biblio.map_authority_linking (bibid BIGINT, marc TEXT y.authority FROM ( SELECT DISTINCT $1 AS bib, BTRIM(remove_paren_substring(txt))::BIGINT AS authority - FROM explode_array(oils_xpath('//*[@code="0"]/text()',$2)) x(txt) + FROM unnest(oils_xpath('//*[@code="0"]/text()',$2)) x(txt) WHERE BTRIM(remove_paren_substring(txt)) ~ $re$^\d+$$re$ ) y JOIN authority.record_entry r ON r.id = y.authority; SELECT $1; @@ -1027,7 +1027,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 fadc392890..9a01e56b3e 100644 --- a/Open-ILS/src/sql/Pg/100.circ_matrix.sql +++ b/Open-ILS/src/sql/Pg/100.circ_matrix.sql @@ -449,7 +449,7 @@ BEGIN END IF; -- Apparently....use the circ matchpoint org unit to determine what org units are valid. - SELECT INTO context_org_list ARRAY_ACCUM(id) FROM actor.org_unit_full_path( circ_matchpoint.org_unit ); + SELECT INTO context_org_list array_agg(id) FROM actor.org_unit_full_path( circ_matchpoint.org_unit ); IF renewal THEN penalty_type = '%RENEW%'; @@ -601,7 +601,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 @@ -780,7 +780,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 @@ -843,7 +843,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 633fb5711f..38c07a3bc8 100644 --- a/Open-ILS/src/sql/Pg/110.hold_matrix.sql +++ b/Open-ILS/src/sql/Pg/110.hold_matrix.sql @@ -224,7 +224,7 @@ DECLARE done BOOL := FALSE; 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; @@ -312,7 +312,7 @@ BEGIN FROM actor.usr_standing_penalty usp JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty) WHERE usr = match_user - AND usp.org_unit IN ( SELECT * FROM explode_array(context_org_list) ) + AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) ) AND (usp.stop_date IS NULL or usp.stop_date > NOW()) AND csp.block_list LIKE '%HOLD%' LOOP @@ -328,7 +328,7 @@ BEGIN FROM actor.usr_standing_penalty usp JOIN config.standing_penalty csp ON (csp.id = usp.standing_penalty) WHERE usr = match_user - AND usp.org_unit IN ( SELECT * FROM explode_array(context_org_list) ) + AND usp.org_unit IN ( SELECT * FROM unnest(context_org_list) ) AND (usp.stop_date IS NULL or usp.stop_date > NOW()) AND csp.block_list LIKE '%CIRC%' LOOP 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 e13bb60075..d7437bb7b3 100644 --- a/Open-ILS/src/sql/Pg/300.schema.staged_search.sql +++ b/Open-ILS/src/sql/Pg/300.schema.staged_search.sql @@ -82,12 +82,12 @@ 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; 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; ELSIF param_search_ou = 0 THEN -- reserved for user lassos (ou_buckets/type='lasso') with ID passed in depth ... hack? sure. END IF; @@ -106,7 +106,7 @@ BEGIN check_count := check_count + 1; - PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM search.explode_array( core_result.records ) ); + PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM unnest( core_result.records ) ); IF NOT FOUND THEN -- RAISE NOTICE ' % were all deleted ... ', core_result.records; deleted_count := deleted_count + 1; @@ -117,7 +117,7 @@ BEGIN FROM biblio.record_entry b JOIN config.bib_source s ON (b.source = s.id) WHERE s.transcendant - AND b.id IN ( SELECT * FROM search.explode_array( core_result.records ) ); + AND b.id IN ( SELECT * FROM unnest( core_result.records ) ); IF FOUND THEN -- RAISE NOTICE ' % were all transcendant ... ', core_result.records; @@ -150,8 +150,8 @@ BEGIN AND cn.label = '##URI##' AND uri.active AND ( param_locations IS NULL OR array_upper(param_locations, 1) IS NULL ) - AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) ) - AND cn.owning_lib IN ( SELECT * FROM search.explode_array( search_org_list ) ) + AND cn.record IN ( SELECT * FROM unnest( core_result.records ) ) + AND cn.owning_lib IN ( SELECT * FROM unnest( search_org_list ) ) LIMIT 1; IF FOUND THEN @@ -184,9 +184,9 @@ BEGIN JOIN asset.copy cp ON (cp.call_number = cn.id) WHERE NOT cn.deleted AND NOT cp.deleted - AND cp.status IN ( SELECT * FROM search.explode_array( param_statuses ) ) - AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) ) - AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) ) + AND cp.status IN ( SELECT * FROM unnest( param_statuses ) ) + AND cn.record IN ( SELECT * FROM unnest( core_result.records ) ) + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) LIMIT 1; IF NOT FOUND THEN @@ -194,9 +194,9 @@ BEGIN FROM biblio.peer_bib_copy_map pr JOIN asset.copy cp ON (cp.id = pr.target_copy) WHERE NOT cp.deleted - AND cp.status IN ( SELECT * FROM search.explode_array( param_statuses ) ) - AND pr.peer_record IN ( SELECT * FROM search.explode_array( core_result.records ) ) - AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) ) + AND cp.status IN ( SELECT * FROM unnest( param_statuses ) ) + AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) ) + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) LIMIT 1; IF NOT FOUND THEN @@ -215,9 +215,9 @@ BEGIN JOIN asset.copy cp ON (cp.call_number = cn.id) WHERE NOT cn.deleted AND NOT cp.deleted - AND cp.location IN ( SELECT * FROM search.explode_array( param_locations ) ) - AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) ) - AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) ) + AND cp.location IN ( SELECT * FROM unnest( param_locations ) ) + AND cn.record IN ( SELECT * FROM unnest( core_result.records ) ) + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) LIMIT 1; IF NOT FOUND THEN @@ -225,9 +225,9 @@ BEGIN FROM biblio.peer_bib_copy_map pr JOIN asset.copy cp ON (cp.id = pr.target_copy) WHERE NOT cp.deleted - AND cp.location IN ( SELECT * FROM search.explode_array( param_locations ) ) - AND pr.peer_record IN ( SELECT * FROM search.explode_array( core_result.records ) ) - AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) ) + AND cp.location IN ( SELECT * FROM unnest( param_locations ) ) + AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) ) + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) LIMIT 1; IF NOT FOUND THEN @@ -243,16 +243,16 @@ BEGIN PERFORM 1 FROM asset.opac_visible_copies - WHERE circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) ) - AND record IN ( SELECT * FROM search.explode_array( core_result.records ) ) + WHERE circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + AND record IN ( SELECT * FROM unnest( core_result.records ) ) LIMIT 1; IF NOT FOUND THEN PERFORM 1 FROM biblio.peer_bib_copy_map pr JOIN asset.opac_visible_copies cp ON (cp.copy_id = pr.target_copy) - WHERE cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) ) - AND pr.peer_record IN ( SELECT * FROM search.explode_array( core_result.records ) ) + WHERE cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) ) LIMIT 1; IF NOT FOUND THEN @@ -270,8 +270,8 @@ BEGIN JOIN asset.copy cp ON (cp.call_number = cn.id) WHERE NOT cn.deleted AND NOT cp.deleted - AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) ) - AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) ) + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + AND cn.record IN ( SELECT * FROM unnest( core_result.records ) ) LIMIT 1; IF NOT FOUND THEN @@ -280,15 +280,15 @@ BEGIN FROM biblio.peer_bib_copy_map pr JOIN asset.copy cp ON (cp.id = pr.target_copy) WHERE NOT cp.deleted - AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) ) - AND pr.peer_record IN ( SELECT * FROM search.explode_array( core_result.records ) ) + AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) ) + AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) ) LIMIT 1; IF NOT FOUND THEN PERFORM 1 FROM asset.call_number cn - WHERE cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) ) + WHERE cn.record IN ( SELECT * FROM unnest( core_result.records ) ) LIMIT 1; IF FOUND THEN diff --git a/Open-ILS/src/sql/Pg/990.schema.unapi.sql b/Open-ILS/src/sql/Pg/990.schema.unapi.sql index 4cfa20cb7f..7f83b667b6 100644 --- a/Open-ILS/src/sql/Pg/990.schema.unapi.sql +++ b/Open-ILS/src/sql/Pg/990.schema.unapi.sql @@ -3,7 +3,7 @@ DROP SCHEMA IF EXISTS unapi CASCADE; BEGIN; CREATE SCHEMA unapi; -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 TABLE unapi.bre_output_layout ( name TEXT PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/999.functions.global.sql b/Open-ILS/src/sql/Pg/999.functions.global.sql index 71c45c3c63..f7c8c0935c 100644 --- a/Open-ILS/src/sql/Pg/999.functions.global.sql +++ b/Open-ILS/src/sql/Pg/999.functions.global.sql @@ -995,7 +995,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 2bffcef310..9752a2359d 100644 --- a/Open-ILS/src/sql/Pg/example.reporter-extension.sql +++ b/Open-ILS/src/sql/Pg/example.reporter-extension.sql @@ -258,7 +258,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, + ARRAY_TO_STRING(array_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; @@ -285,7 +285,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, + ARRAY_TO_STRING(array_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; @@ -313,7 +313,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, + ARRAY_TO_STRING(array_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/pgmemcache-ou_tree_traversal_cache.sql b/Open-ILS/src/sql/Pg/pgmemcache-ou_tree_traversal_cache.sql index 53eea2e8ed..1559947aab 100644 --- a/Open-ILS/src/sql/Pg/pgmemcache-ou_tree_traversal_cache.sql +++ b/Open-ILS/src/sql/Pg/pgmemcache-ou_tree_traversal_cache.sql @@ -69,7 +69,7 @@ BEGIN FOR curr_org IN SELECT * FROM actor.org_unit - WHERE id IN ( SELECT * FROM explode_array( STRING_TO_ARRAY( cached_value.x, ',' ) ) ) + WHERE id IN ( SELECT * FROM unnest( STRING_TO_ARRAY( cached_value.x, ',' ) ) ) LOOP RETURN NEXT curr_org; END LOOP; diff --git a/Open-ILS/src/sql/Pg/reporter-schema.sql b/Open-ILS/src/sql/Pg/reporter-schema.sql index 85b5c54457..1485743f24 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 + ARRAY_TO_STRING(array_agg( DISTINCT publisher.value), ', ') AS publisher, + ARRAY_TO_STRING(array_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.11.0