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

Hi Stan,

On Sun, Sep 15, 2019 at 2:47 PM stan <stanb(at)panix(dot)com> 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/

<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
Libre
de virus. www.avast.com
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>