1 CREATE OR REPLACE FUNCTION migration_tools.attempt_hz_date (TEXT,TEXT) RETURNS DATE AS $$
3 attempt_value ALIAS FOR $1;
4 fail_value ALIAS FOR $2;
8 EXECUTE E'SELECT (\'1970-01-01\'::date + \'' || attempt_value || E' days\'::interval)::date AS a;'
15 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::date AS a;'
20 $$ LANGUAGE PLPGSQL STRICT STABLE;
22 CREATE OR REPLACE FUNCTION migration_tools.attempt_sierra_timestamp (TEXT,TEXT) RETURNS TIMESTAMP AS $$
24 attempt_value ALIAS FOR $1;
25 fail_value ALIAS FOR $2;
28 output := REGEXP_REPLACE(attempt_value,E'^(..)(..)(..)(..)(..)$',E'20\\1-\\2-\\3 \\4:\\5')::TIMESTAMP;
33 EXECUTE 'SELECT ' || quote_literal(fail_value) || '::TIMESTAMP AS a;'
38 $$ LANGUAGE PLPGSQL STRICT STABLE;
40 CREATE OR REPLACE FUNCTION migration_tools.openbiblio2marc (x_bibid TEXT) RETURNS TEXT AS $func$
42 -- Expects the following table/columns:
46 -- l_create_dt | 2007-03-07 09:03:09
47 -- l_last_change_dt | 2015-01-23 11:18:54
48 -- l_last_change_userid | 2
50 -- l_collection_cd | 13
51 -- l_call_nmbr1 | Canada
53 -- l_call_nmbr3 | Ottawa 18
54 -- l_title | Art and the courts : France ad England
55 -- l_title_remainder | from 1259-1328
56 -- l_responsibility_stmt |
57 -- l_author | National Gallery of Canada
64 -- l_flag_attention | 0
66 -- export_biblio_field_tsv:
73 -- l_field_data | Brieger, Peter Henry
75 -- Map export_biblio_tsv as follows:
76 -- l_call_nmbr? -> 099a
79 -- l_title_remainder -> 245b
80 -- l_responsibility_stmt -> 245c
85 migration_tools.make_stub_bib(y.tag,y.ind1,y.ind2,y.data)
88 array_agg(lpad(l_tag,3,'0') || l_subfield_cd) as "tag",
89 array_agg(l_ind1_cd) as "ind1",
90 array_agg(l_ind2_cd) as "ind2",
91 array_agg(l_field_data) as "data"
99 from export_biblio_field_tsv
100 where l_bibid = x_bibid
104 'a' as "l_subfield_cd",
108 nullif(btrim(l_call_nmbr1),''),
109 nullif(btrim(l_call_nmbr2),''),
110 nullif(btrim(l_call_nmbr3),'')
112 from export_biblio_tsv
113 where l_bibid = x_bibid
117 'a' as "l_subfield_cd",
120 l_author as "l_field_data"
121 from export_biblio_tsv
122 where l_bibid = x_bibid and nullif(btrim(l_author),'') is not null
126 'a' as "l_subfield_cd",
129 l_title as "l_field_data"
130 from export_biblio_tsv
131 where l_bibid = x_bibid and nullif(btrim(l_title),'') is not null
135 'b' as "l_subfield_cd",
138 l_title_remainder as "l_field_data"
139 from export_biblio_tsv
140 where l_bibid = x_bibid and nullif(btrim(l_title_remainder),'') is not null
144 'a' as "l_subfield_cd",
147 l_topic1 as "l_field_data"
148 from export_biblio_tsv
149 where l_bibid = x_bibid and nullif(btrim(l_topic1),'') is not null
153 'a' as "l_subfield_cd",
156 l_topic2 as "l_field_data"
157 from export_biblio_tsv
158 where l_bibid = x_bibid and nullif(btrim(l_topic2),'') is not null
162 'a' as "l_subfield_cd",
165 l_topic3 as "l_field_data"
166 from export_biblio_tsv
167 where l_bibid = x_bibid and nullif(btrim(l_topic3),'') is not null
171 'a' as "l_subfield_cd",
174 l_topic4 as "l_field_data"
175 from export_biblio_tsv
176 where l_bibid = x_bibid and nullif(btrim(l_topic4),'') is not null
180 'a' as "l_subfield_cd",
183 l_topic5 as "l_field_data"
184 from export_biblio_tsv
185 where l_bibid = x_bibid and nullif(btrim(l_topic5),'') is not null
189 '' as "l_subfield_cd",
192 l_bibid as "l_field_data"
193 from export_biblio_tsv
194 where l_bibid = x_bibid
199 $func$ LANGUAGE plpgsql;