On 9/16/19 11:52 AM, stan wrote:
On Mon, Sep 16, 2019 at 12:12:34AM -0400, Tom Lane wrote:
stan <stanb@xxxxxxxxx> writes:
On Sun, Sep 15, 2019 at 12:27:14PM -0700, Adrian Klaver wrote:
On 9/15/19 10:46 AM, stan wrote:
So, my test tell me that the validity check is done BEFORE an attempt to
insert (thus firing the trigger) occurs.
What validity check?
The check to see if it is the type enum.
Indeed, a trigger cannot fix an input-validity error, because that
will happen while trying to form the row value that would be passed
to the trigger. So I guess that when you say "the trigger doesn't
fire" you really mean "this other error is raised first".
However, I still don't understand your claim that it works the
way you wanted in an INSERT statement. The enum input function
is going to complain in either context.
Generally you need to fix issues like this before trying to
insert the data into your table. You might try preprocessing
the data file before feeding it to COPY. Another way is to
copy into a temporary table that has very lax column data types
(all "text", perhaps) and then transform the data using
INSERT ... SELECT from the temp table to the final storage table.
regards, tom lane
Thanks for educating me. I thought I had tested and seen that this worked on
an INSERT, but once you told me it does not, I re tested to convince myself
that my test was invalid. let me show you what I was trying to do:
So was it invalid?
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 %', NEW.status;
RAISE NOTICE 'Left With With NEW.c_type %', NEW.c_type;
*/
return NEW;
END;
$$
LANGUAGE PLPGSQL;
CREATE TRIGGER fix_customer_types_case_trig BEFORE INSERT OR UPDATE ON customer
FOR EACH ROW EXECUTE FUNCTION fix_customer_types_case();
all of this is to deal with columns defined as this user defined type.
CREATE TYPE activity_status AS ENUM ('ACTIVE' ,
'INACTIVE');
Can you think of a better way to make the system "human data entry friendly"?
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
