Message-ID: <4F5F91F6.2020103@turnkey.bz>
Date: Tue, 13 Mar 2012 12:29:10 -0600
From: "M. D." <lists@turnkey.bz>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:10.0.2) Gecko/20120216 Thunderbird/10.0.2
MIME-Version: 1.0
To: "PostgreSQL (SQL)" <pgsql-sql@postgresql.org>
Subject: help on a function with exception
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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();