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

On Mon, Sep 16, 2019 at 12:12:34AM -0400, Tom Lane wrote:
> stan <stanb(at)panix(dot)com> 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:

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"?

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