Re: help on a function with exception

On Mar 13, 2012, at 14:29, "M. D." <lists@turnkey.bz> wrote:

> Hi,
>
> I want to do a check on a column if other columns meet certain conditions. The program I'm working with allows to
createadditional columns on every 'object' - called extra data, but I have no control over the program.  I want to
enforcethe values on this one extra data to be of type date. 
>
> My idea was to do a Trigger function and cast to a date and if there's an exception, raise an error.  Below is what
I'vetried, but it just keeps on Raising Exception. 
>
> Could someone please help me? The date I enter is: 2012-10-10 which works fine if I do a:
> select '2012-10-10'::date
>
> Thanks
>
> --Postgres 9.0
>
> CREATE OR REPLACE FUNCTION fnc_check_PO_extra_date()
>  RETURNS trigger AS
> $BODY$
> DECLARE
>    tmp_date date;
> BEGIN
>  IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN
>    IF (NEW.data_value IS NOT NULL or new.data_value = '') and NEW.extra_id =
>        (select extra_id from extra_data where data_type = 9
>            and (data_name = 'ETA' or data_name = 'Adjusted ETA'))
>    THEN
>        tmp_date := new.data_value::date;
>    END IF;
>  END IF;
>  EXCEPTION
>    WHEN others THEN
>    RAISE EXCEPTION 'Invalid date on Extra Data!';
> return NEW;
> END;
> $BODY$
> LANGUAGE plpgsql VOLATILE
>
> CREATE TRIGGER trg_check_PO_extra_date
>  BEFORE INSERT OR UPDATE
>  ON extra_values
>  FOR EACH ROW
>  EXECUTE PROCEDURE fnc_check_PO_extra_date();
>
>

You are suppressing the original exception so figuring out what is wrong is very difficult.

Your IF allows new.data_value to be the empty string which, iirc,  cannot be cast to date

David J.