On 9/15/19 10:46 AM, stan wrote:
Forgot to cc the list again. Have to look at settings in mutt.
Sorry forgot to cc the list
On Sun, Sep 15, 2019 at 08:42:02AM -0700, Adrian Klaver wrote:
On 9/15/19 8:33 AM, stan wrote:
I have defined this function:
CREATE FUNCTION fix_customer_types_case()
and this trigger:
CREATE TRIGGER fix_customer_types_case_trig BEFORE INSERT OR UPDATE ON customer
FOR EACH ROW EXECUTE FUNCTION fix_customer_types_case();
and I put a RAISE NOTICE so I can tell if the function is called. Yet when I
do a :
\copy to bring data into this table, I do not see the notice.
What is the actual command you are using?
\COPY customer(name, location, status , c_type , bill_attention , bill_addresse , bill_address_1 , bill_address_2 , bill_city , bill_state , bill_country , ship_attention , ship_addresse , ship_address_1 , ship_address_2, ship_city ,ship_state ) from '/home/stan/pm_db/live_data/ready/customer.csv' DELIMITER ',' CSV HEADER ;
and here is the function
CREATE FUNCTION fix_customer_types_case()
RETURNS trigger AS $$
BEGIN
if NEW.c_type IS NOT NULL
THEN
NEW.c_type := upper(cast( NEW.c_type AS TEXT));
END IF ;
if NEW.status IS NOT NULL
THEN
RAISE NOTICE 'Called With %', NEW.status;
NEW.status := upper(cast( NEW.status AS TEXT));
END IF ;
RAISE NOTICE 'Left With With %', NEW.status;
RAISE NOTICE 'Left With With %', NEW.c_type;
return NEW;
END;
$$
LANGUAGE PLPGSQL;
if I do an insert this function is called. However it IS NOT called for the
above copy command. How can I fix that?
I thought you said it was fixed now.
I discovered that the function was not getting defined, and fixed that. Then I
rashly posted to the list that it was fixed, as i was certain that was the
only issue. But after I reported that, I tried testing, with he results in
this email.
Works for INSERT, but does not fire on this \copy command.
More interesting data. I used vi to correct the incorrect case in the CSV file
being imported, and re-ran the \copy command. At this point in time, I did
see the messages from notice. I deleted the rows, re-edited back to the
incorrect case in the csv file, and the import ((\copy) failed.
So, my test tell me that the validity check is done BEFORE an attempt to
insert (thus firing the trigger) occurs.
What validity check?
Interesting, but not helpful for my application.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
