Re: FW: Re: FW: Re: Shouldn;t this trigger be called? - Mailing list 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