Thread: FW: Re: Shouldn;t this trigger be called?
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? 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. -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
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