1 -- Rather than polluting the public schema with general Evergreen
2 -- functions, carve out a dedicated schema
4 DROP SCHEMA IF EXISTS evergreen CASCADE;
8 CREATE SCHEMA evergreen;
10 CREATE OR REPLACE FUNCTION evergreen.change_db_setting(setting_name TEXT, settings TEXT[]) RETURNS VOID AS $$
12 EXECUTE 'ALTER DATABASE ' || quote_ident(current_database()) || ' SET ' || quote_ident(setting_name) || ' = ' || array_to_string(settings, ',');
16 SELECT evergreen.change_db_setting('search_path', ARRAY['evergreen','public','pg_catalog']);
18 CREATE OR REPLACE FUNCTION evergreen.array_remove_item_by_value(inp ANYARRAY, el ANYELEMENT) RETURNS anyarray AS $$ SELECT ARRAY_AGG(x.e) FROM UNNEST( $1 ) x(e) WHERE x.e <> $2; $$ LANGUAGE SQL;
20 CREATE OR REPLACE FUNCTION evergreen.lowercase( TEXT ) RETURNS TEXT AS $$
22 $$ LANGUAGE PLPERLU STRICT IMMUTABLE;
24 CREATE OR REPLACE FUNCTION evergreen.uppercase( TEXT ) RETURNS TEXT AS $$
26 $$ LANGUAGE PLPERLU STRICT IMMUTABLE;
28 CREATE OR REPLACE FUNCTION evergreen.xml_escape(str TEXT) RETURNS text AS $$
29 SELECT REPLACE(REPLACE(REPLACE($1,
33 $$ LANGUAGE SQL IMMUTABLE;
35 CREATE OR REPLACE FUNCTION evergreen.regexp_split_to_array(TEXT, TEXT)
37 return encode_array_literal([split $_[1], $_[0]]);
38 $$ LANGUAGE PLPERLU STRICT IMMUTABLE;
40 -- Provide a named type for patching functions
41 CREATE TYPE evergreen.patch AS (patch TEXT);
43 CREATE OR REPLACE FUNCTION evergreen.xml_pretty_print(input XML)
47 SELECT xslt_process($1::text,
48 $$<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
50 <xsl:output method="xml" omit-xml-declaration="yes" indent="yes"/>
51 <xsl:strip-space elements="*"/>
52 <xsl:template match="@*|node()">
54 <xsl:apply-templates select="@*|node()"/>
61 COMMENT ON FUNCTION evergreen.xml_pretty_print(input XML) IS
62 'Simple pretty printer for XML, as written by Andrew Dunstan at http://goo.gl/zBHIk';
64 CREATE OR REPLACE FUNCTION evergreen.could_be_serial_holding_code(TEXT) RETURNS BOOL AS $$
69 my $holding_code = (new JSON::XS)->decode(shift);
70 new MARC::Field('999', @$holding_code);
73 # verify that subfield labels are exactly one character long
74 foreach (keys %{ { @$holding_code } }) {
75 return 0 if length($_) != 1;
80 COMMENT ON FUNCTION evergreen.could_be_serial_holding_code(TEXT) IS
81 'Return true if parameter is valid JSON representing an array that at minimum doesn''t make MARC::Field balk and only has subfield labels exactly one character long. Otherwise false.';
83 CREATE OR REPLACE FUNCTION evergreen.protect_reserved_rows_from_delete() RETURNS trigger AS $protect_reserved$
85 IF OLD.id < TG_ARGV[0]::INT THEN
86 RAISE EXCEPTION 'Cannot delete row with reserved ID %', OLD.id;
93 CREATE OR REPLACE FUNCTION evergreen.unaccent_and_squash ( IN arg text) RETURNS text
94 IMMUTABLE STRICT AS $$
96 RETURN evergreen.lowercase(public.unaccent('public.unaccent', regexp_replace(arg, '[\s[:punct:]]','','g')));