From d80e70c488b572fa197f15f43e2a818b032585f1 Mon Sep 17 00:00:00 2001 From: Ben Shum Date: Fri, 18 May 2012 00:27:24 -0400 Subject: [PATCH] More native SQL functions Found four more places that this needed to be changed. Signed-off-by: Ben Shum Signed-off-by: Dan Scott --- Open-ILS/examples/fm_IDL.xml | 14 +++++++------- Open-ILS/src/sql/Pg/002.functions.aggregate.sql | 4 ++-- Open-ILS/src/sql/Pg/002.functions.config.sql | 2 +- Open-ILS/src/sql/Pg/300.schema.staged_search.sql | 2 +- 4 files changed, 11 insertions(+), 11 deletions(-) diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index a5fd4249b6..5d180adbf8 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -3702,7 +3702,7 @@ SELECT usr, SELECT usr, - ARRAY_TO_STRING(ARRAY_ACCUM( + STRING_AGG( CASE WHEN ( ((fine_interval >= '1 day' AND due_date >= 'today') OR (fine_interval < '1 day' AND due_date > 'now')) @@ -3710,9 +3710,9 @@ SELECT usr, ) THEN id ELSE 0 END - ),',') AS out, + ,',') AS out, - ARRAY_TO_STRING(ARRAY_ACCUM( + STRING_AGG( CASE WHEN ( ((fine_interval >= '1 day' AND due_date < 'today') OR (fine_interval < '1 day' AND due_date < 'now')) @@ -3720,11 +3720,11 @@ SELECT usr, ) THEN id ELSE 0 END - ),',') AS overdue, + ,',') 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 + STRING_AGG( CASE WHEN (xact_finish IS NULL AND stop_fines = 'LOST') THEN id ELSE 0 END,',') AS lost, + STRING_AGG( CASE WHEN (xact_finish IS NULL AND stop_fines = 'CLAIMSRETURNED') THEN id ELSE 0 END,',') AS claims_returned, + STRING_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/sql/Pg/002.functions.aggregate.sql b/Open-ILS/src/sql/Pg/002.functions.aggregate.sql index cb42cd7b06..57f0ce570f 100644 --- a/Open-ILS/src/sql/Pg/002.functions.aggregate.sql +++ b/Open-ILS/src/sql/Pg/002.functions.aggregate.sql @@ -17,12 +17,12 @@ BEGIN; -DROP AGGREGATE IF EXISTS array_accum(anyelement) CASCADE; +DROP AGGREGATE IF EXISTS array_agg(anyelement) CASCADE; DROP AGGREGATE IF EXISTS public.first(anyelement) CASCADE; DROP AGGREGATE IF EXISTS public.last(anyelement) CASCADE; DROP AGGREGATE IF EXISTS public.agg_text(text) CASCADE; -CREATE AGGREGATE array_accum ( +CREATE AGGREGATE array_agg ( sfunc = array_append, basetype = anyelement, stype = anyarray, diff --git a/Open-ILS/src/sql/Pg/002.functions.config.sql b/Open-ILS/src/sql/Pg/002.functions.config.sql index 2e5de49161..13f7b35462 100644 --- a/Open-ILS/src/sql/Pg/002.functions.config.sql +++ b/Open-ILS/src/sql/Pg/002.functions.config.sql @@ -26,7 +26,7 @@ CREATE OR REPLACE FUNCTION oils_xml_transform ( TEXT, TEXT ) RETURNS TEXT AS $_$ $_$ LANGUAGE SQL STRICT IMMUTABLE; CREATE OR REPLACE FUNCTION public.extract_marc_field ( TEXT, BIGINT, TEXT, TEXT ) RETURNS TEXT AS $$ - SELECT regexp_replace(array_to_string( array_accum( output ),' ' ),$4,'','g') FROM oils_xpath_table('id', 'marc', $1, $3, 'id='||$2)x(id INT, output TEXT); + SELECT regexp_replace(string_agg(output,' '),$4,'','g') FROM oils_xpath_table('id', 'marc', $1, $3, 'id='||$2)x(id INT, output TEXT); $$ LANGUAGE SQL; CREATE OR REPLACE FUNCTION oils_xml_uncache (xml TEXT) RETURNS BOOL AS $func$ 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 477d5d08c8..67740cf2e5 100644 --- a/Open-ILS/src/sql/Pg/300.schema.staged_search.sql +++ b/Open-ILS/src/sql/Pg/300.schema.staged_search.sql @@ -102,7 +102,7 @@ BEGIN END IF; IF param_pref_ou IS NOT NULL THEN - SELECT array_accum(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors(param_pref_ou); + SELECT array_agg(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors(param_pref_ou); luri_org_list := luri_org_list || tmp_int_list; END IF; -- 2.43.2