· 7 years ago · Feb 08, 2019, 06:06 AM
1DROP TABLE IF EXISTS temp_bib_record_metadata
2;
3
4
5-- creating temp table of the record ids, and the bib record numbers that we're interested in
6-- (campus_code being blank indicates that the record belongs to us, and not a virtual one (interlibrary loan functions)
7CREATE TEMP TABLE temp_bib_record_metadata AS
8SELECT
9r.id,
10r.record_num as bib_record_num,
11r.creation_date_gmt::date as creation_date,
12r.record_last_updated_gmt::date as record_last_updated
13
14FROM
15sierra_view.record_metadata AS r
16
17WHERE
18r.record_type_code = 'b'
19AND r.campus_code = ''
20AND r.deletion_date_gmt IS NULL
21;
22
23
24DROP TABLE IF EXISTS temp_bib_export
25;
26
27
28-- give this query some extra time to complete (60000 milliseconds = 1 minute)
29-- (3600000 milliseconds = 60 minutes)
30set statement_timeout to 3600000; commit
31;
32
33
34CREATE TEMP TABLE temp_bib_export AS
35SELECT
36r.bib_record_num,
37r.id as bib_record_id,
38(
39 SELECT
40 string_agg(po.index_entry, ',' ORDER BY po.occurrence, po.id)
41
42 FROM
43 sierra_view.phrase_entry as po
44
45 WHERE
46 po.record_id = r.id
47 AND po.index_tag = 'o'
48 AND po.varfield_type_code = 'o'
49) AS control_numbers,
50r.creation_date,
51r.record_last_updated,
52(
53 SELECT
54 regexp_matches(
55 --regexp_replace(trim(v.field_content), '(\|[a-z]{1})', '', 'ig'), -- get the call number strip the subfield indicators
56 v.field_content,
57 '[0-9]{9,10}[x]{0,1}|[0-9]{12,13}[x]{0,1}', -- the regex to match on (10 or 13 digits, with the possibility of the 'X' character in the check-digit spot)
58 'i' -- regex flags; ignore case
59 )
60
61 FROM
62 sierra_view.varfield as v
63
64 WHERE
65 v.record_id = r.id
66 AND v.marc_tag || v.varfield_type_code = '020i'
67
68 ORDER BY
69 v.occ_num
70
71 LIMIT 1
72
73)[1]::varchar(30) as isbn,
74p.best_author,
75p.best_author_norm,
76p.best_title,
77p.best_title_norm,
78-- publisher is not not normalized as our sierra instance doesn't index this field
79(
80 SELECT
81 s.content
82 FROM
83 sierra_view.subfield as s
84
85 WHERE
86 s.record_id = r.id
87 AND s.field_type_code = 'p'
88 AND s.tag = 'b'
89
90 ORDER BY
91 s.display_order
92
93 LIMIT 1
94) as publisher,
95p.publish_year,
96-- the normalized call number on the bib
97(
98 SELECT
99 pc.index_entry
100
101 FROM
102 sierra_view.phrase_entry as pc
103
104 WHERE
105 pc.record_id = r.id
106 AND pc.index_tag = 'c'
107 AND pc.varfield_type_code = 'c'
108
109 ORDER BY
110 pc.id
111
112 LIMIT 1
113) as bib_level_callnumber,
114
115
116-- -- This is the previous method used to extract call number for the bib record
117-- (
118-- SELECT
119-- TRIM(s.content || ' ' || COALESCE(sb.content, ''))
120-- FROM
121-- sierra_view.subfield as s
122--
123-- LEFT OUTER JOIN
124-- sierra_view.subfield as sb
125-- ON
126-- sb.record_id = r.id
127-- AND sb.field_type_code = 'c'
128-- AND sb.tag = 'b'
129--
130-- WHERE
131-- s.record_id = r.id
132-- AND s.field_type_code = 'c'
133-- AND s.tag = 'a'
134--
135-- ORDER BY
136-- s.display_order
137--
138-- LIMIT 1
139-- ) as bib_level_callnumber_prev,
140-- indexed_subjects are subject headings coming from the normalized and indexed sierra fields for fields tagged 'Subject'
141(
142 SELECT
143 string_agg(p.index_entry, ',' ORDER BY p.occurrence, p.id) as subject
144
145 FROM
146 sierra_view.phrase_entry as p
147
148 WHERE
149 p.record_id = r.id
150 -- index tag d is subject
151 AND p.index_tag = 'd'
152) as indexed_subjects
153
154FROM
155temp_bib_record_metadata as r
156
157JOIN
158sierra_view.bib_record_property as p
159ON
160 p.bib_record_id = r.id
161
162-- TESTING -- only fetch a small sample
163-- LIMIT 10000
164;
165
166-- TESTING -- select data from the temp table for output
167-- SELECT * FROM temp_bib_export
168-- ;