Re: FW: Re: FW: Re: Shouldn;t this trigger be called?
От | Adrian Klaver |
---|---|
Тема | Re: FW: Re: FW: Re: Shouldn;t this trigger be called? |
Дата | |
Msg-id | 50d5b256-74b9-1949-a634-0df37cbfb783@aklaver.com обсуждение исходный текст |
Ответ на | FW: Re: FW: Re: Shouldn;t this trigger be called? (stan <stanb@panix.com>) |
Ответы |
Re: FW: Re: FW: Re: Shouldn;t this trigger be called?
|
Список | pgsql-general |
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@aklaver.com
В списке pgsql-general по дате отправления:
Следующее
От: Olivier GautherotДата:
Сообщение: Re: FW: Re: FW: Re: Shouldn;t this trigger be called?