On Mar 13, 2012, at 14:29, "M. D." <li...@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 create additional columns on every > 'object' - called extra data, but I have no control over the program. I want > to enforce the 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've tried, 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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
