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

On 9/15/19 8:55 AM, stan wrote:
> 
> 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.

> 
> This line from the page you referenced implies this should work, but i must be
> doing something wrong:
> 
> COPY FROM will invoke any triggers and check constraints on the destination
> table. However, it will not invoke rules.
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com