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

On 9/16/19 11:52 AM, stan wrote:
> On Mon, Sep 16, 2019 at 12:12:34AM -0400, Tom Lane wrote:
>> stan <stanb@panix.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:
> 

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@aklaver.com