Hi, I've been working the past couple of days to import a bunch of csv data
into my system.
I have a temp_holding table which basically just matches the csv format.
I have a trigger before insert on each row on the temp_holding table, below.
The problem is that inserting into temp_holding takes more than a SECOND PER
ROW!
What can I do to speed this up, because importing 106 records is going to take
a day and a half???
Kind regards,
John Hughes
CREATE FUNCTION data_import() RETURNS "trigger"
AS '
DECLARE
User RECORD;
check RECORD;
BEGIN
SELECT INTO check * FROM core_contacts WHERE primaryphone = NEW.number; --
Checks for duplicate entries
IF NOT FOUND THEN
SELECT INTO User id FROM core_users WHERE username = NEW.username;
IF User.id IS NULL THEN
User.id := 37;
END IF;
INSERT INTO core_contacts (
primaryphone,
zip,
regionid,
city,
address,
firstname,
lastname)
VALUES (
NEW.number,
NEW.zip,
(SELECT id FROM core_regions WHERE name = NEW.state),
NEW.city,
NEW.street,
NEW.first_name,
NEW.last_name);
INSERT INTO core_leads (contactid, leadstatustypeid, createdbyuserid,
leadtypeid, notes)
VALUES ( max(core_contacts.id),
1,
User.id,
1,
NEW.agent_comments );
IF NEW.lead_date = '''' THEN
NEW.lead_date := ''now'';
END IF;
INSERT INTO core_leadevents ( leadid, leadeventtypeid, userid, created,
notes )
VALUES ( max(core_leads.id),
4,
User.id,
date(NEW.lead_date),
''Imported on ''+''now'' );
INSERT INTO core_leadattributes (value, attributetypeid, leadid)
VALUES ( NEW.loan_amount, 18, max(core_leads.id));
INSERT INTO core_leadattributes (value, attributetypeid, leadid)
VALUES ( NEW.property_value, 1, max(core_leads.id));
INSERT INTO core_leadattributes (value, attributetypeid, leadid)
VALUES ( NEW.interest_rate, 14, max(core_leads.id));
INSERT INTO core_leadattributes (value, attributetypeid, leadid)
VALUES ( NEW.interest_type, 13, max(core_leads.id));
ELSE
NEW.duplicate = true;
END IF;
RETURN NEW;
END;
'
LANGUAGE plpgsql;
