functions.adoc

Functions

Table of Contents

• auth_name
• auth_refname_base
• csid_from_group_id
• csid_from_id
• deurn
• shortid_auth
• id_from_csid

auth_name

Given an authority refname URN, returns the authority name.

create or replace function auth_name(refname varchar)
returns varchar
language plpgsql
as
$Q$
declare
  authname varchar;
begin
  authname := substring(refname from '^urn:.*:name\(([^)]+)\):item:name');
  return authname;
end;
$Q$;

auth_refname_base

Given an authority refname URN, returns the URN with the display name removed from the end. This is useful for matching variant term usages to the term record.

create or replace function auth_refname_base(refname varchar)
returns varchar
language plpgsql
as
$Q$
declare
  base varchar;
begin
  base := substring(refname from '^urn:.*item:name\([^)]+\)');
  return base;
end;
$Q$;

csid_from_group_id

Takes the id from a row in a field group table (such as annotationgroup), and returns the CSID of the record the field group is part of.

CREATE OR REPLACE FUNCTION CSID_FROM_GROUP_ID(groupid varchar) RETURNS varchar LANGUAGE PLPGSQL AS $$
declare csid varchar;
begin
select hh.name
into csid
from hierarchy h
left join hierarchy hh on h.parentid = hh.id
where h.id = groupid;

return csid;
end;
$$;

csid_from_id

Returns the UI/API CSID for a record’s database internal ID

Given: d5e13ea2-13f8-41cc-9713-dd32d36dc689 Expect: 5dffaecc-c80f-4966-a84c

create or replace function csid_from_id(dbid varchar)
returns varchar
language plpgsql
as
$$
declare
   csid varchar;
begin
   select hierarchy.name
   into csid
   from hierarchy
   where hierarchy.id = dbid;

   return csid;
end;
$$;

deurn

Extract value from refname URN

create or replace function deurn(urn varchar)
returns varchar
language plpgsql
as
$Q$
declare
  deurned varchar;
begin
  deurned := REGEXP_REPLACE(
	  REGEXP_REPLACE(
	  REGEXP_REPLACE(urn, $$^urn:.*item:name\([^)]+\)$$, ''),
	  '''$', ''),
	  $$^'$$, '');
  return deurned;
end;
$Q$;

shortid_auth

Extract shortId value from refname URN for an authority term

create or replace function shortid_auth(urn varchar)
returns varchar
language plpgsql
as
$Q$
declare
  sid varchar;
begin
  sid := substring(urn from '^urn:.*item:name\(([^)]+)\)');
  return sid;
end;
$Q$;

id_from_csid

Returns the database internal id from the CSID found at the end of a record URI in the application.

create or replace function id_from_csid(csid varchar)
returns varchar
language plpgsql
as
$$
declare
   dbid varchar;
begin
   select hierarchy.id
   into dbid
   from hierarchy
   where hierarchy.name = csid;

   return dbid;
end;
$$;