From 59a0819eb92268c7ac51f45959a4b9305876c7a0 Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Wed, 28 Sep 2011 14:15:43 -0400 Subject: [PATCH] Update upgrade script with new _refs functions (and supporting fuctions) Signed-off-by: Mike Rylander --- .../src/sql/Pg/upgrade/XXXX.schema-acs-nfi.sql | 179 ++++++++++++++++----- 1 file changed, 137 insertions(+), 42 deletions(-) diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema-acs-nfi.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema-acs-nfi.sql index e0de950160..9f5762f748 100644 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema-acs-nfi.sql +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema-acs-nfi.sql @@ -322,19 +322,6 @@ BEGIN END; $$ LANGUAGE PLPGSQL ROWS 10; -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((SELECT ARRAY_ACCUM(field) FROM authority.browse_axis_authority_field_map WHERE axis = $1), $2, $3, $4) -$$ LANGUAGE SQL ROWS 10; - -CREATE OR REPLACE FUNCTION authority.btag_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((SELECT ARRAY_ACCUM(authority_field) FROM authority.control_set_bib_field WHERE tag = $1), $2, $3, $4) -$$ LANGUAGE SQL ROWS 10; - -CREATE OR REPLACE FUNCTION authority.atag_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((SELECT ARRAY_ACCUM(id) FROM authority.control_set_authority_field WHERE tag = $1), $2, $3, $4) -$$ LANGUAGE SQL ROWS 10; - - CREATE OR REPLACE FUNCTION authority.simple_heading_browse_top( atag_list INT[], q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ DECLARE pivot_sort_value TEXT; @@ -371,19 +358,6 @@ BEGIN END; $$ LANGUAGE PLPGSQL ROWS 10; -CREATE OR REPLACE FUNCTION authority.axis_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ - SELECT * FROM authority.simple_heading_browse_top((SELECT ARRAY_ACCUM(field) FROM authority.browse_axis_authority_field_map WHERE axis = $1), $2, $3, $4) -$$ LANGUAGE SQL ROWS 10; - -CREATE OR REPLACE FUNCTION authority.btag_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ - SELECT * FROM authority.simple_heading_browse_top((SELECT ARRAY_ACCUM(authority_field) FROM authority.control_set_bib_field WHERE tag = $1), $2, $3, $4) -$$ LANGUAGE SQL ROWS 10; - -CREATE OR REPLACE FUNCTION authority.atag_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ - SELECT * FROM authority.simple_heading_browse_top((SELECT ARRAY_ACCUM(id) FROM authority.control_set_authority_field WHERE tag = $1), $2, $3, $4) -$$ LANGUAGE SQL ROWS 10; - - CREATE OR REPLACE FUNCTION authority.simple_heading_search_rank( atag_list INT[], q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ SELECT ash.id FROM authority.simple_heading ash, @@ -398,19 +372,6 @@ CREATE OR REPLACE FUNCTION authority.simple_heading_search_rank( atag_list INT[] OFFSET $4 * $3; $$ LANGUAGE SQL ROWS 10; -CREATE OR REPLACE FUNCTION authority.axis_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ - SELECT * FROM authority.simple_heading_search_rank((SELECT ARRAY_ACCUM(field) FROM authority.browse_axis_authority_field_map WHERE axis = $1), $2, $3, $4) -$$ LANGUAGE SQL ROWS 10; - -CREATE OR REPLACE FUNCTION authority.btag_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ - SELECT * FROM authority.simple_heading_search_rank((SELECT ARRAY_ACCUM(authority_field) FROM authority.control_set_bib_field WHERE tag = $1), $2, $3, $4) -$$ LANGUAGE SQL ROWS 10; - -CREATE OR REPLACE FUNCTION authority.atag_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ - SELECT * FROM authority.simple_heading_search_rank((SELECT ARRAY_ACCUM(id) FROM authority.control_set_authority_field WHERE tag = $1), $2, $3, $4) -$$ LANGUAGE SQL ROWS 10; - - CREATE OR REPLACE FUNCTION authority.simple_heading_search_heading( atag_list INT[], q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ SELECT ash.id FROM authority.simple_heading ash, @@ -423,16 +384,150 @@ CREATE OR REPLACE FUNCTION authority.simple_heading_search_heading( atag_list IN OFFSET $4 * $3; $$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.axis_authority_tags(a TEXT) RETURNS INT[] AS $$ + 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 +$$ 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) + ) + FROM authority.control_set_bib_field a + WHERE a.tag = $1 +$$ 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) + ) + FROM authority.control_set_authority_field a + WHERE a.tag = $1 +$$ 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; + +CREATE OR REPLACE FUNCTION authority.btag_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.btag_authority_tags($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.atag_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.atag_authority_tags($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.axis_browse_center_refs( 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_refs($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.btag_browse_center_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_browse_center(authority.btag_authority_tags_refs($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.atag_browse_center_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_browse_center(authority.atag_authority_tags_refs($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + + +CREATE OR REPLACE FUNCTION authority.axis_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_browse_top(authority.axis_authority_tags($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.btag_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_browse_top(authority.btag_authority_tags($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.atag_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_browse_top(authority.atag_authority_tags($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.axis_browse_top_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_browse_top(authority.axis_authority_tags_refs($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.btag_browse_top_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_browse_top(authority.btag_authority_tags_refs($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.atag_browse_top_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_browse_top(authority.atag_authority_tags_refs($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + + +CREATE OR REPLACE FUNCTION authority.axis_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_search_rank(authority.axis_authority_tags($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.btag_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_search_rank(authority.btag_authority_tags($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.atag_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_search_rank(authority.atag_authority_tags($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.axis_search_rank_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_search_rank(authority.axis_authority_tags_refs($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.btag_search_rank_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_search_rank(authority.btag_authority_tags_refs($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.atag_search_rank_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_search_rank(authority.atag_authority_tags_refs($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + + CREATE OR REPLACE FUNCTION authority.axis_search_heading( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ - SELECT * FROM authority.simple_heading_search_heading((SELECT ARRAY_ACCUM(field) FROM authority.browse_axis_authority_field_map WHERE axis = $1), $2, $3, $4) + SELECT * FROM authority.simple_heading_search_heading(authority.axis_authority_tags($1), $2, $3, $4) $$ LANGUAGE SQL ROWS 10; CREATE OR REPLACE FUNCTION authority.btag_search_heading( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ - SELECT * FROM authority.simple_heading_search_heading((SELECT ARRAY_ACCUM(authority_field) FROM authority.control_set_bib_field WHERE tag = $1), $2, $3, $4) + SELECT * FROM authority.simple_heading_search_heading(authority.btag_authority_tags($1), $2, $3, $4) $$ LANGUAGE SQL ROWS 10; CREATE OR REPLACE FUNCTION authority.atag_search_heading( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ - SELECT * FROM authority.simple_heading_search_heading((SELECT ARRAY_ACCUM(id) FROM authority.control_set_authority_field WHERE tag = $1), $2, $3, $4) + SELECT * FROM authority.simple_heading_search_heading(authority.atag_authority_tags($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.axis_search_heading_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_search_heading(authority.axis_authority_tags_refs($1), $2, $3, $4) $$ LANGUAGE SQL ROWS 10; +CREATE OR REPLACE FUNCTION authority.btag_search_heading_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_search_heading(authority.btag_authority_tags_refs($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.atag_search_heading_refs( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_search_heading(authority.atag_authority_tags_refs($1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + + COMMIT; + -- 2.11.0