\c - eepstein

CREATE OR REPLACE FUNCTION "merchandise".trg_insert_ds_temp() 
    RETURNS trigger AS '
BEGIN
    IF ( NEW."dsrc_id" IS NULL) THEN
        NEW."dsrc_id" = merchandise.ds_get_id(''MASTER'');
    END IF;
    IF ( NEW."base_dsrc_id" IS NULL) THEN
        NEW."base_dsrc_id" = NEW."dsrc_id";
    END IF;
    IF ( NEW."decimal_value_1" IS NULL) THEN
        NEW."decimal_value_1" = 100;     -- sort_order
    END IF;
    RETURN NEW;
END; 
  ' LANGUAGE plpgsql VOLATILE;
  
\echo ' ...Setting up default values for dsrc_id, base_dsrc_id and decimal_value_1 columns...'
CREATE TRIGGER zz_set_dsrc_id_temp BEFORE INSERT ON "merchandise"."item"
    FOR EACH ROW EXECUTE PROCEDURE "merchandise".trg_insert_ds_temp();

BEGIN TRANSACTION;

DELETE FROM "merchandise"."item" 
       WHERE "dsrc_id"=merchandise.ds_get_id('MASTER');

\echo ' ...Inserting (via COPY) new item records...'
COPY "merchandise"."item" ("client_key", "varchar_value_1", "varchar_value_2", "varchar_value_3", "decimal_value_2", "varchar_value_4", "varchar_value_5", "varchar_value_6", "varchar_value_7", "varchar_value_8", "varchar_value_9", "varchar_value_10", "varchar_value_11", "timestamptz_value_1", "varchar_value_12", "decimal_value_3", "varchar_value_13", "decimal_value_4", "decimal_value_5", "varchar_value_14", "varchar_value_15", "varchar_value_16", "varchar_value_17", "boolean_value_1", "varchar_value_18") 
FROM '/cygdrive/e/Prajna/Development/Clients/InterSight/src/5.0/client/yurman/data/dy-test-data-121603.txt'
    WITH DELIMITER AS '\t' NULL AS '';
       
SELECT count(*) from merchandise.item;

COMMIT TRANSACTION;

DROP TRIGGER zz_set_dsrc_id_temp ON "merchandise"."item" CASCADE;
DROP FUNCTION "merchandise".trg_insert_ds_temp() CASCADE;
       
SELECT count(*) from merchandise.item;

\c - pw_dbo