1 -- Evergreen DB patch 0598.schema.vandelay_one_match_per.sql
6 -- check whether patch can be applied
7 SELECT evergreen.upgrade_deps_block_check('0598', :eg_version);
9 CREATE OR REPLACE FUNCTION vandelay.match_set_test_marcxml(
10 match_set_id INTEGER, record_xml TEXT
11 ) RETURNS SETOF vandelay.match_set_test_result AS $$
22 tags_rstore := vandelay.flatten_marc_hstore(record_xml);
23 svf_rstore := vandelay.extract_rec_attrs(record_xml);
25 CREATE TEMPORARY TABLE _vandelay_tmp_qrows (q INTEGER);
26 CREATE TEMPORARY TABLE _vandelay_tmp_jrows (j TEXT);
28 -- generate the where clause and return that directly (into wq), and as
29 -- a side-effect, populate the _vandelay_tmp_[qj]rows tables.
30 wq := vandelay.get_expr_from_match_set(match_set_id);
32 query_ := 'SELECT DISTINCT(bre.id) AS record, ';
34 -- qrows table is for the quality bits we add to the SELECT clause
35 SELECT ARRAY_TO_STRING(
36 ARRAY_ACCUM('COALESCE(n' || q::TEXT || '.quality, 0)'), ' + '
37 ) INTO coal FROM _vandelay_tmp_qrows;
39 -- our query string so far is the SELECT clause and the inital FROM.
40 -- no JOINs yet nor the WHERE clause
41 query_ := query_ || coal || ' AS quality ' || E'\n' ||
42 'FROM biblio.record_entry bre ';
44 -- jrows table is for the joins we must make (and the real text conditions)
45 SELECT ARRAY_TO_STRING(ARRAY_ACCUM(j), E'\n') INTO joins
46 FROM _vandelay_tmp_jrows;
48 -- add those joins and the where clause to our query.
49 query_ := query_ || joins || E'\n' || 'WHERE ' || wq || ' AND not bre.deleted';
51 -- this will return rows of record,quality
52 FOR rec IN EXECUTE query_ USING tags_rstore, svf_rstore LOOP
56 DROP TABLE _vandelay_tmp_qrows;
57 DROP TABLE _vandelay_tmp_jrows;