Hi Stan,
On Sun, Sep 15, 2019 at 2:47 PM stan <stanb@xxxxxxxxx> 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.
Interesting, but not helpful for my application.
--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin
Maybe you could try awk on your input: https://thomas-cokelaer.info/blog/2018/01/awk-convert-into-lower-or-upper-cases/
