X-Git-Url: http://git.equinoxoli.org/?p=migration-tools.git;a=blobdiff_plain;f=sql%2Fbase%2Fbase.sql;h=cb096d0c928c7bf2b6857510f04b6714547aa117;hp=14d0dcd3cec064923f957fec0e0201fb098b0a49;hb=b152406444c1dabacfa2da3b8fc2cd9434fd314f;hpb=4e6df79299c8fb45bcab705ab284582d067ef7f0 diff --git a/sql/base/base.sql b/sql/base/base.sql index 14d0dcd..cb096d0 100644 --- a/sql/base/base.sql +++ b/sql/base/base.sql @@ -1,3 +1,19 @@ +-- Copyright 2009-2012, Equinox Software, Inc. +-- +-- This program is free software; you can redistribute it and/or +-- modify it under the terms of the GNU General Public License +-- as published by the Free Software Foundation; either version 2 +-- of the License, or (at your option) any later version. +-- +-- This program is distributed in the hope that it will be useful, +-- but WITHOUT ANY WARRANTY; without even the implied warranty of +-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +-- GNU General Public License for more details. +-- +-- You should have received a copy of the GNU General Public License +-- along with this program; if not, write to the Free Software +-- Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. + -------------------------------------------------------------------------- -- An example of how to use: -- @@ -606,7 +622,7 @@ CREATE OR REPLACE FUNCTION migration_tools.expand_barcode (TEXT, TEXT, INTEGER, return $barcode if (length($prefix) + length($new_barcode) + length($suffix)) > $maxlen; return "$prefix$new_barcode$suffix"; -$$ LANGUAGE PLPERL STABLE; +$$ LANGUAGE PLPERLU STABLE; CREATE OR REPLACE FUNCTION migration_tools.attempt_cast (TEXT,TEXT,TEXT) RETURNS RECORD AS $$ DECLARE @@ -713,7 +729,7 @@ CREATE OR REPLACE FUNCTION migration_tools.add_codabar_checkdigit (TEXT) RETURNS my $remainder = $total % 10; my $checkdigit = ($remainder == 0) ? $remainder : 10 - $remainder; return $barcode . $checkdigit; -$$ LANGUAGE PLPERL STRICT STABLE; +$$ LANGUAGE PLPERLU STRICT STABLE; CREATE OR REPLACE FUNCTION migration_tools.attempt_phone (TEXT,TEXT) RETURNS TEXT AS $$ DECLARE @@ -939,6 +955,220 @@ END; $$ LANGUAGE plpgsql; +CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_before_20( tablename TEXT ) RETURNS VOID AS $$ + +-- Usage: +-- +-- First make sure the circ matrix is loaded and the circulations +-- have been staged to the extent possible (but at the very least +-- circ_lib, target_copy, usr, and *_renewal). User profiles and +-- circ modifiers must also be in place. +-- +-- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation'); +-- + +DECLARE + circ_lib INT; + target_copy INT; + usr INT; + is_renewal BOOLEAN; + this_duration_rule INT; + this_fine_rule INT; + this_max_fine_rule INT; + rcd config.rule_circ_duration%ROWTYPE; + rrf config.rule_recurring_fine%ROWTYPE; + rmf config.rule_max_fine%ROWTYPE; + circ INT; + n INT := 0; + n_circs INT; + +BEGIN + + EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs; + + FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP + + -- Fetch the correct rules for this circulation + EXECUTE (' + SELECT + circ_lib, + target_copy, + usr, + CASE + WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE + ELSE FALSE + END + FROM ' || tablename || ' WHERE id = ' || circ || ';') + INTO circ_lib, target_copy, usr, is_renewal ; + SELECT + INTO this_duration_rule, + this_fine_rule, + this_max_fine_rule + duration_rule, + recuring_fine_rule, + max_fine_rule + FROM action.find_circ_matrix_matchpoint( + circ_lib, + target_copy, + usr, + is_renewal + ); + SELECT INTO rcd * FROM config.rule_circ_duration + WHERE id = this_duration_rule; + SELECT INTO rrf * FROM config.rule_recurring_fine + WHERE id = this_fine_rule; + SELECT INTO rmf * FROM config.rule_max_fine + WHERE id = this_max_fine_rule; + + -- Apply the rules to this circulation + EXECUTE ('UPDATE ' || tablename || ' c + SET + duration_rule = rcd.name, + recuring_fine_rule = rrf.name, + max_fine_rule = rmf.name, + duration = rcd.normal, + recuring_fine = rrf.normal, + max_fine = + CASE rmf.is_percent + WHEN TRUE THEN (rmf.amount / 100.0) * ac.price + ELSE rmf.amount + END, + renewal_remaining = rcd.max_renewals + FROM + config.rule_circ_duration rcd, + config.rule_recuring_fine rrf, + config.rule_max_fine rmf, + asset.copy ac + WHERE + rcd.id = ' || this_duration_rule || ' AND + rrf.id = ' || this_fine_rule || ' AND + rmf.id = ' || this_max_fine_rule || ' AND + ac.id = c.target_copy AND + c.id = ' || circ || ';'); + + -- Keep track of where we are in the process + n := n + 1; + IF (n % 100 = 0) THEN + RAISE INFO '%', n || ' of ' || n_circs + || ' (' || (100*n/n_circs) || '%) circs updated.'; + END IF; + + END LOOP; + + RETURN; +END; + +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION migration_tools.apply_circ_matrix_after_20( tablename TEXT ) RETURNS VOID AS $$ + +-- Usage: +-- +-- First make sure the circ matrix is loaded and the circulations +-- have been staged to the extent possible (but at the very least +-- circ_lib, target_copy, usr, and *_renewal). User profiles and +-- circ modifiers must also be in place. +-- +-- SELECT migration_tools.apply_circ_matrix('m_pioneer.action_circulation'); +-- + +DECLARE + circ_lib INT; + target_copy INT; + usr INT; + is_renewal BOOLEAN; + this_duration_rule INT; + this_fine_rule INT; + this_max_fine_rule INT; + rcd config.rule_circ_duration%ROWTYPE; + rrf config.rule_recurring_fine%ROWTYPE; + rmf config.rule_max_fine%ROWTYPE; + circ INT; + n INT := 0; + n_circs INT; + +BEGIN + + EXECUTE 'SELECT COUNT(*) FROM ' || tablename || ';' INTO n_circs; + + FOR circ IN EXECUTE ('SELECT id FROM ' || tablename) LOOP + + -- Fetch the correct rules for this circulation + EXECUTE (' + SELECT + circ_lib, + target_copy, + usr, + CASE + WHEN phone_renewal OR desk_renewal OR opac_renewal THEN TRUE + ELSE FALSE + END + FROM ' || tablename || ' WHERE id = ' || circ || ';') + INTO circ_lib, target_copy, usr, is_renewal ; + SELECT + INTO this_duration_rule, + this_fine_rule, + this_max_fine_rule + (matchpoint).duration_rule, + (matchpoint).recurring_fine_rule, + (matchpoint).max_fine_rule + FROM action.find_circ_matrix_matchpoint( + circ_lib, + target_copy, + usr, + is_renewal + ); + SELECT INTO rcd * FROM config.rule_circ_duration + WHERE id = this_duration_rule; + SELECT INTO rrf * FROM config.rule_recurring_fine + WHERE id = this_fine_rule; + SELECT INTO rmf * FROM config.rule_max_fine + WHERE id = this_max_fine_rule; + + -- Apply the rules to this circulation + EXECUTE ('UPDATE ' || tablename || ' c + SET + duration_rule = rcd.name, + recurring_fine_rule = rrf.name, + max_fine_rule = rmf.name, + duration = rcd.normal, + recurring_fine = rrf.normal, + max_fine = + CASE rmf.is_percent + WHEN TRUE THEN (rmf.amount / 100.0) * ac.price + ELSE rmf.amount + END, + renewal_remaining = rcd.max_renewals, + grace_period = rrf.grace_period + FROM + config.rule_circ_duration rcd, + config.rule_recurring_fine rrf, + config.rule_max_fine rmf, + asset.copy ac + WHERE + rcd.id = ' || this_duration_rule || ' AND + rrf.id = ' || this_fine_rule || ' AND + rmf.id = ' || this_max_fine_rule || ' AND + ac.id = c.target_copy AND + c.id = ' || circ || ';'); + + -- Keep track of where we are in the process + n := n + 1; + IF (n % 100 = 0) THEN + RAISE INFO '%', n || ' of ' || n_circs + || ' (' || (100*n/n_circs) || '%) circs updated.'; + END IF; + + END LOOP; + + RETURN; +END; + +$$ LANGUAGE plpgsql; + + + + CREATE OR REPLACE FUNCTION migration_tools.stage_not_applicable_asset_stat_cats( schemaname TEXT ) RETURNS VOID AS $$ -- USAGE: Make sure the stat_cat and stat_cat_entry tables are populated, including exactly one 'Not Applicable' entry per stat cat. @@ -1052,7 +1282,7 @@ DECLARE BEGIN - EXECUTE ('TRUNCATE ' || c || ';'); + EXECUTE ('DELETE FROM ' || c || ';'); EXECUTE ('INSERT INTO ' || c || ' (usr, barcode) SELECT id, usrname FROM ' || u || ';'); EXECUTE ('UPDATE ' || u || ' u SET card = c.id FROM ' || c || ' c WHERE c.usr = u.id;'); @@ -1078,7 +1308,7 @@ CREATE OR REPLACE FUNCTION migration_tools.insert_856_9 (TEXT, TEXT) RETURNS TEX my $marc = MARC::Record->new_from_xml($marcxml, 'UTF-8'); foreach my $field ( $marc->field('856') ) { - if ( scalar(grep( /(netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 && + if ( scalar(grep( /(contentreserve|netlibrary|overdrive)\.com/i, $field->subfield('u'))) > 0 && ! ( $field->as_string('9') =~ m/$shortname/ ) ) { $field->add_subfields( '9' => $shortname ); $field->update( ind2 => '0'); @@ -1185,7 +1415,6 @@ CREATE OR REPLACE FUNCTION migration_tools.zip_to_city_state_county (TEXT) RETUR $$ LANGUAGE PLPERLU STABLE; - CREATE OR REPLACE FUNCTION migration_tools.check_ou_depth ( ) RETURNS VOID AS $$ DECLARE @@ -1198,25 +1427,62 @@ DECLARE parent_shortname TEXT; ou_type_name TEXT; parent_type TEXT; + type_id INT; + type_depth INT; + type_parent INT; + type_parent_depth INT; + proper_parent TEXT; BEGIN errors_found := FALSE; - FOR ou IN EXECUTE ('SELECT DISTINCT id FROM actor.org_unit ORDER BY 1;') LOOP +-- Checking actor.org_unit_type + + FOR type_id IN EXECUTE ('SELECT id FROM actor.org_unit_type ORDER BY id;') LOOP + + SELECT depth FROM actor.org_unit_type WHERE id = type_id INTO type_depth; + SELECT parent FROM actor.org_unit_type WHERE id = type_id INTO type_parent; + + IF type_parent IS NOT NULL THEN + + SELECT depth FROM actor.org_unit_type WHERE id = type_parent INTO type_parent_depth; + + IF type_depth - type_parent_depth <> 1 THEN + SELECT name FROM actor.org_unit_type WHERE id = type_id INTO ou_type_name; + SELECT name FROM actor.org_unit_type WHERE id = type_parent INTO parent_type; + RAISE INFO 'The % org unit type has a depth of %, but its parent org unit type, %, has a depth of %.', + ou_type_name, type_depth, parent_type, type_parent_depth; + errors_found := TRUE; + + END IF; + + END IF; + + END LOOP; + +-- Checking actor.org_unit + + FOR ou IN EXECUTE ('SELECT id FROM actor.org_unit ORDER BY shortname;') LOOP SELECT parent_ou FROM actor.org_unit WHERE id = ou INTO ou_parent; - SELECT depth FROM actor.org_unit_type WHERE id = ou INTO org_unit_depth; - SELECT depth FROM actor.org_unit_type WHERE id = ou_parent INTO parent_depth; - - IF ou_parent IS NOT NULL AND org_unit_depth - parent_depth <> 1 THEN - SELECT shortname FROM actor.org_unit WHERE id = ou INTO ou_shortname; - SELECT shortname FROM actor.org_unit WHERE id = ou_parent INTO parent_shortname; - SELECT t.name FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou INTO ou_type_name; - SELECT t.name FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou_parent INTO parent_type; - RAISE INFO '% (org unit %) is a % (depth %) but its parent, % (org unit %), is a % (depth %).', - ou_shortname, ou, ou_type_name, org_unit_depth, parent_shortname, ou_parent, parent_type, parent_depth; - errors_found := TRUE; + SELECT t.depth FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou INTO org_unit_depth; + SELECT t.depth FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou_parent INTO parent_depth; + SELECT shortname FROM actor.org_unit WHERE id = ou INTO ou_shortname; + SELECT shortname FROM actor.org_unit WHERE id = ou_parent INTO parent_shortname; + SELECT t.name FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou INTO ou_type_name; + SELECT t.name FROM actor.org_unit_type t, actor.org_unit o WHERE o.ou_type = t.id and o.id = ou_parent INTO parent_type; + + IF ou_parent IS NOT NULL THEN + + IF (org_unit_depth - parent_depth <> 1) OR ( + (SELECT parent FROM actor.org_unit_type WHERE name = ou_type_name) <> (SELECT id FROM actor.org_unit_type WHERE name = parent_type) + ) THEN + RAISE INFO '% (org unit %) is a % (depth %) but its parent, % (org unit %), is a % (depth %).', + ou_shortname, ou, ou_type_name, org_unit_depth, parent_shortname, ou_parent, parent_type, parent_depth; + errors_found := TRUE; + END IF; + END IF; END LOOP; @@ -1230,3 +1496,230 @@ BEGIN END; $$ LANGUAGE plpgsql; + + +CREATE OR REPLACE FUNCTION migration_tools.refresh_opac_visible_copies ( ) RETURNS VOID AS $$ + +BEGIN + + DELETE FROM asset.opac_visible_copies; + + INSERT INTO asset.opac_visible_copies (id, circ_lib, record) + SELECT DISTINCT + cp.id, cp.circ_lib, cn.record + FROM + asset.copy cp + JOIN asset.call_number cn ON (cn.id = cp.call_number) + JOIN actor.org_unit a ON (cp.circ_lib = a.id) + JOIN asset.copy_location cl ON (cp.location = cl.id) + JOIN config.copy_status cs ON (cp.status = cs.id) + JOIN biblio.record_entry b ON (cn.record = b.id) + WHERE + NOT cp.deleted AND + NOT cn.deleted AND + NOT b.deleted AND + cs.opac_visible AND + cl.opac_visible AND + cp.opac_visible AND + a.opac_visible AND + cp.id NOT IN (SELECT id FROM asset.opac_visible_copies); + +END; + +$$ LANGUAGE plpgsql; + + +CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owning_lib INTEGER) RETURNS VOID AS $$ + +DECLARE + old_volume BIGINT; + new_volume BIGINT; + bib BIGINT; + old_owning_lib INTEGER; + old_label TEXT; + remainder BIGINT; + +BEGIN + + -- Gather information + SELECT call_number INTO old_volume FROM asset.copy WHERE id = copy_id; + SELECT record INTO bib FROM asset.call_number WHERE id = old_volume; + SELECT owning_lib, label INTO old_owning_lib, old_label FROM asset.call_number WHERE id = old_volume; + + -- Bail out if the new_owning_lib is not the ID of an org_unit + IF new_owning_lib NOT IN (SELECT id FROM actor.org_unit) THEN + RAISE WARNING + '% is not a valid actor.org_unit ID; no change made.', + new_owning_lib; + RETURN; + END IF; + + -- Bail out discreetly if the owning_lib is already correct + IF new_owning_lib = old_owning_lib THEN + RETURN; + END IF; + + -- Check whether we already have a destination volume available + SELECT id INTO new_volume FROM asset.call_number + WHERE + record = bib AND + owning_lib = new_owning_lib AND + label = old_label AND + NOT deleted; + + -- Create destination volume if needed + IF NOT FOUND THEN + INSERT INTO asset.call_number (creator, editor, record, owning_lib, label) + VALUES (1, 1, bib, new_owning_lib, old_label); + SELECT id INTO new_volume FROM asset.call_number + WHERE + record = bib AND + owning_lib = new_owning_lib AND + label = old_label AND + NOT deleted; + END IF; + + -- Move copy to destination + UPDATE asset.copy SET call_number = new_volume WHERE id = copy_id; + + -- Delete source volume if it is now empty + SELECT id INTO remainder FROM asset.copy WHERE call_number = old_volume AND NOT deleted; + IF NOT FOUND THEN + DELETE FROM asset.call_number WHERE id = old_volume; + END IF; + +END; + +$$ LANGUAGE plpgsql; + + +CREATE OR REPLACE FUNCTION migration_tools.change_owning_lib(copy_id BIGINT, new_owner TEXT) RETURNS VOID AS $$ + +-- You can use shortnames with this function, which looks up the org unit ID and passes it to change_owning_lib(BIGINT,INTEGER). + +DECLARE + new_owning_lib INTEGER; + +BEGIN + + -- Parse the new_owner as an org unit ID or shortname + IF new_owner IN (SELECT shortname FROM actor.org_unit) THEN + SELECT id INTO new_owning_lib FROM actor.org_unit WHERE shortname = new_owner; + PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib); + ELSIF new_owner ~ E'^[0-9]+$' THEN + IF new_owner::INTEGER IN (SELECT id FROM actor.org_unit) THEN + RAISE INFO + '%', + E'You don\'t need to put the actor.org_unit ID in quotes; ' + || E'if you put it in quotes, I\'m going to try to parse it as a shortname first.'; + new_owning_lib := new_owner::INTEGER; + PERFORM migration_tools.change_owning_lib(copy_id, new_owning_lib); + END IF; + ELSE + RAISE WARNING + '% is not a valid actor.org_unit shortname or ID; no change made.', + new_owning_lib; + RETURN; + END IF; + +END; + +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION migration_tools.marc_parses( TEXT ) RETURNS BOOLEAN AS $func$ + +use MARC::Record; +use MARC::File::XML (BinaryEncoding => 'UTF-8'); +use MARC::Charset; + +MARC::Charset->assume_unicode(1); + +my $xml = shift; + +eval { my $r = MARC::Record->new_from_xml( $xml ); }; +if ($@) { + return 0; +} else { + return 1; +} + +$func$ LANGUAGE PLPERLU; +COMMENT ON FUNCTION migration_tools.marc_parses(TEXT) IS 'Return boolean indicating if MARCXML string is parseable by MARC::File::XML'; + +CREATE OR REPLACE FUNCTION migration_tools.simple_export_library_config(dir TEXT, orgs INT[]) RETURNS VOID AS $FUNC$ +BEGIN + EXECUTE $$COPY (SELECT * FROM actor.hours_of_operation WHERE id IN ($$ || + ARRAY_TO_STRING(orgs, ',') || $$)$$ || + $$) TO '$$ || dir || $$/actor_hours_of_operation'$$; + EXECUTE $$COPY (SELECT org_unit, close_start, close_end, reason FROM actor.org_unit_closed WHERE org_unit IN ($$ || + ARRAY_TO_STRING(orgs, ',') || $$)$$ || + $$) TO '$$ || dir || $$/actor_org_unit_closed'$$; + EXECUTE $$COPY (SELECT org_unit, name, value FROM actor.org_unit_setting WHERE org_unit IN ($$ || + ARRAY_TO_STRING(orgs, ',') || $$)$$ || + $$) TO '$$ || dir || $$/actor_org_unit_setting'$$; + EXECUTE $$COPY (SELECT name, owning_lib, holdable, hold_verify, opac_visible, circulate FROM asset.copy_location WHERE owning_lib IN ($$ || + ARRAY_TO_STRING(orgs, ',') || $$)$$ || + $$) TO '$$ || dir || $$/asset_copy_location'$$; + EXECUTE $$COPY (SELECT grp, org_unit, penalty, threshold FROM permission.grp_penalty_threshold WHERE org_unit IN ($$ || + ARRAY_TO_STRING(orgs, ',') || $$)$$ || + $$) TO '$$ || dir || $$/permission_grp_penalty_threshold'$$; + EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_prefix WHERE owning_lib IN ($$ || + ARRAY_TO_STRING(orgs, ',') || $$)$$ || + $$) TO '$$ || dir || $$/asset_call_number_prefix'$$; + EXECUTE $$COPY (SELECT owning_lib, label, label_sortkey FROM asset.call_number_suffix WHERE owning_lib IN ($$ || + ARRAY_TO_STRING(orgs, ',') || $$)$$ || + $$) TO '$$ || dir || $$/asset_call_number_suffix'$$; + EXECUTE $$COPY config.rule_circ_duration TO '$$ || dir || $$/config_rule_circ_duration'$$; + EXECUTE $$COPY config.rule_age_hold_protect TO '$$ || dir || $$/config_age_hold_protect'$$; + EXECUTE $$COPY config.rule_max_fine TO '$$ || dir || $$/config_rule_max_fine'$$; + EXECUTE $$COPY config.rule_recurring_fine TO '$$ || dir || $$/config_rule_recurring_fine'$$; +END; +$FUNC$ LANGUAGE PLPGSQL; + +CREATE OR REPLACE FUNCTION migration_tools.simple_import_library_config(dir TEXT) RETURNS VOID AS $FUNC$ +BEGIN + EXECUTE $$COPY actor.hours_of_operation FROM '$$ || dir || $$/actor_hours_of_operation'$$; + EXECUTE $$COPY actor.org_unit_closed (org_unit, close_start, close_end, reason) FROM '$$ || dir || $$/actor_org_unit_closed'$$; + EXECUTE $$COPY actor.org_unit_setting (org_unit, name, value) FROM '$$ || dir || $$/actor_org_unit_setting'$$; + EXECUTE $$COPY asset.copy_location (name, owning_lib, holdable, hold_verify, opac_visible, circulate) FROM '$$ || dir || $$/asset_copy_location'$$; + EXECUTE $$COPY permission.grp_penalty_threshold (grp, org_unit, penalty, threshold) FROM '$$ || dir || $$/permission_grp_penalty_threshold'$$; + EXECUTE $$COPY asset.call_number_prefix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_prefix'$$; + EXECUTE $$COPY asset.call_number_suffix (owning_lib, label, label_sortkey) FROM '$$ || dir || $$/asset_call_number_suffix'$$; + + -- import any new circ rules + PERFORM migration_tools.simple_import_new_rows_by_value(cir, 'config', 'rule_circ_duration', 'id', 'name'); + PERFORM migration_tools.simple_import_new_rows_by_value(cir, 'config', 'rule_age_hold_protect', 'id', 'name'); + PERFORM migration_tools.simple_import_new_rows_by_value(cir, 'config', 'rule_max_fine', 'id', 'name'); + PERFORM migration_tools.simple_import_new_rows_by_value(cir, 'config', 'rule_recurring_fine', 'id', 'name'); + +END; +$FUNC$ LANGUAGE PLPGSQL; + + +CREATE OR REPLACE FUNCTION migration_tools.simple_import_new_rows_by_value(dir TEXT, schemaname TEXT, tablename TEXT, idcol TEXT, matchcol TEXT) RETURNS VOID AS $FUNC$ +DECLARE + name TEXT; + loopq TEXT; + existsq TEXT; + ct INTEGER; + cols TEXT[]; + copyst TEXT; +BEGIN + EXECUTE $$DROP TABLE IF EXISTS tmp_$$ || tablename; + EXECUTE $$CREATE TEMPORARY TABLE tmp_$$ || tablename || $$ AS SELECT * FROM $$ || schemaname || '.' || tablename || $$ LIMIT 0$$; + EXECUTE $$COPY tmp_$$ || tablename || $$ FROM '$$ || dir || '/' || schemaname || '_' || tablename || $$'$$; + loopq := 'SELECT ' || matchcol || ' FROM tmp_' || tablename || ' ORDER BY ' || idcol; + existsq := 'SELECT COUNT(*) FROM ' || schemaname || '.' || tablename || ' WHERE ' || matchcol || ' = $1'; + SELECT ARRAY_AGG(column_name::TEXT) INTO cols FROM information_schema.columns WHERE table_schema = schemaname AND table_name = tablename AND column_name <> idcol; + FOR name IN EXECUTE loopq LOOP + EXECUTE existsq INTO ct USING name; + IF ct = 0 THEN + RAISE NOTICE 'inserting %.% row for %', schemaname, tablename, name; + copyst := 'INSERT INTO ' || schemaname || '.' || tablename || ' (' || ARRAY_TO_STRING(cols, ',') || ') SELECT ' || ARRAY_TO_STRING(cols, ',') || + ' FROM tmp_' || tablename || ' WHERE ' || matchcol || ' = $1'; + EXECUTE copyst USING name; + END IF; + END LOOP; +END; +$FUNC$ LANGUAGE PLPGSQL; +