From 5e7b8ec026ae3fdcd4cb35d413c7fa9a024e4e6a Mon Sep 17 00:00:00 2001
From: Anthony Bucci <anthony@bucci.onl>
Date: Fri, 16 May 2025 22:17:30 +0000
Subject: [PATCH] DRYD-1748: Add deurn function to database (#464)

* DRYD-1748: added SQL script to define deurn function
* DRYD-1748: modified tenant bindings to run deurn_function.sql

---------

Co-authored-by: Anthony Bucci <abucci@bucci.onl>
---
 .../config/services/tenants/tenant-bindings-proto-unified.xml | 4 ++++
 .../src/main/resources/db/postgresql/deurn_function.sql       | 3 +++
 2 files changed, 7 insertions(+)
 create mode 100644 services/common/src/main/resources/db/postgresql/deurn_function.sql

diff --git a/services/common/src/main/cspace/config/services/tenants/tenant-bindings-proto-unified.xml b/services/common/src/main/cspace/config/services/tenants/tenant-bindings-proto-unified.xml
index 762f8d6e4..ad0e626e8 100644
--- a/services/common/src/main/cspace/config/services/tenants/tenant-bindings-proto-unified.xml
+++ b/services/common/src/main/cspace/config/services/tenants/tenant-bindings-proto-unified.xml
@@ -75,6 +75,10 @@
 						<service:key>sqlScriptName</service:key>
 						<service:value>unaccent_text_search_configuration.sql</service:value>
 					</service:property>
+					<service:property>
+						<service:key>sqlScriptName</service:key>
+						<service:value>deurn_function.sql</service:value>
+					</service:property>
 				</service:params>
 			</service:initHandler>
 		</tenant:serviceBindings>
diff --git a/services/common/src/main/resources/db/postgresql/deurn_function.sql b/services/common/src/main/resources/db/postgresql/deurn_function.sql
new file mode 100644
index 000000000..155ce99fa
--- /dev/null
+++ b/services/common/src/main/resources/db/postgresql/deurn_function.sql
@@ -0,0 +1,3 @@
+-- Convert a URN (often refname) (DRYD-1748: Add deurn function to database)
+CREATE OR REPLACE FUNCTION deurn(urnstr VARCHAR) RETURNS VARCHAR
+AS $BODY$ BEGIN RETURN ((regexp_match(urnstr, '''([^''\\]*(\\.[^''\\]*)*)'''))[1]); END $BODY$ LANGUAGE plpgsql IMMUTABLE;
-- 
2.47.3