Re: FW: Re: FW: Re: Shouldn;t this trigger be called?

On Sun, Sep 15, 2019 at 12:27:14PM -0700, Adrian Klaver wrote:
> 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?
> 

The check to see if it is the type enum.
-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                        -- Benjamin Franklin