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(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:
>

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(dot)klaver(at)aklaver(dot)com