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

On Sun, Sep 15, 2019 at 08:59:43AM -0700, Adrian Klaver wrote:

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.

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.

--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin