From c21157f4ab34946714d9aca18a302e3fbb1a1ae0 Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Mon, 10 Feb 2014 15:14:08 -0500 Subject: [PATCH] Ensure we get a name and are duplicate free in the baseline schema as well Signed-off-by: Mike Rylander --- Open-ILS/src/sql/Pg/990.schema.unapi.sql | 14 ++++++++------ 1 file changed, 8 insertions(+), 6 deletions(-) diff --git a/Open-ILS/src/sql/Pg/990.schema.unapi.sql b/Open-ILS/src/sql/Pg/990.schema.unapi.sql index dd7eaea529..a73289a1b6 100644 --- a/Open-ILS/src/sql/Pg/990.schema.unapi.sql +++ b/Open-ILS/src/sql/Pg/990.schema.unapi.sql @@ -109,14 +109,15 @@ $$ LANGUAGE SQL STABLE; CREATE OR REPLACE FUNCTION evergreen.located_uris ( - bibid BIGINT, + bibid BIGINT, ouid INT, pref_lib INT DEFAULT NULL ) RETURNS TABLE (id BIGINT, name TEXT, label_sortkey TEXT, rank INT) AS $$ WITH all_orgs AS (SELECT COALESCE( enabled, FALSE ) AS flag FROM config.global_flag WHERE name = 'opac.located_uri.act_as_copy') - SELECT acn.id, aou.name, acn.label_sortkey, evergreen.rank_ou(aou.id, $2, $3) AS pref_ou + SELECT DISTINCT ON (id) * FROM ( + SELECT acn.id, COALESCE(aou.name,aoud.name), acn.label_sortkey, evergreen.rank_ou(aou.id, $2, $3) AS pref_ou FROM asset.call_number acn - INNER JOIN asset.uri_call_number_map auricnm ON acn.id = auricnm.call_number + INNER JOIN asset.uri_call_number_map auricnm ON acn.id = auricnm.call_number INNER JOIN asset.uri auri ON auri.id = auricnm.uri LEFT JOIN actor.org_unit_ancestors( COALESCE($3, $2) ) aou ON (acn.owning_lib = aou.id) LEFT JOIN actor.org_unit_descendants( COALESCE($3, $2) ) aoud ON (acn.owning_lib = aoud.id), @@ -126,9 +127,9 @@ CREATE OR REPLACE FUNCTION evergreen.located_uris ( AND auri.active IS TRUE AND ((NOT all_orgs.flag AND aou.id IS NOT NULL) OR COALESCE(aou.id,aoud.id) IS NOT NULL) UNION - SELECT acn.id, aou.name, acn.label_sortkey, evergreen.rank_ou(aou.id, $2, $3) AS pref_ou + SELECT acn.id, COALESCE(aou.name,aoud.name) AS name, acn.label_sortkey, evergreen.rank_ou(aou.id, $2, $3) AS pref_ou FROM asset.call_number acn - INNER JOIN asset.uri_call_number_map auricnm ON acn.id = auricnm.call_number + INNER JOIN asset.uri_call_number_map auricnm ON acn.id = auricnm.call_number INNER JOIN asset.uri auri ON auri.id = auricnm.uri LEFT JOIN actor.org_unit_ancestors( $2 ) aou ON (acn.owning_lib = aou.id) LEFT JOIN actor.org_unit_descendants( $2 ) aoud ON (acn.owning_lib = aoud.id), @@ -136,7 +137,8 @@ CREATE OR REPLACE FUNCTION evergreen.located_uris ( WHERE acn.record = $1 AND acn.deleted IS FALSE AND auri.active IS TRUE - AND ((NOT all_orgs.flag AND aou.id IS NOT NULL) OR COALESCE(aou.id,aoud.id) IS NOT NULL) + AND ((NOT all_orgs.flag AND aou.id IS NOT NULL) OR COALESCE(aou.id,aoud.id) IS NOT NULL))x + ORDER BY id, pref_ou DESC; $$ LANGUAGE SQL STABLE; -- 2.11.0