DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=DKIM-Signature:X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:References:In-Reply-To:Mime-Version:Content-Transfer-Encoding:Content-Type:Message-Id:Cc:X-Mailer:From:Subject:Date:To;
	b=G4UroI0O2fUTa1A7m0xSXducTCVhaWiWq/+/Pu9VDNikRwy1kO31VRopTArVRlXlJ3UeUIDJpFNQd1sIX3XE1blUGOABrI5ITqQN4HwyTDRBveM3o6LQFC0et7nnNjkTi0ANeOEdT7cemBbXAaGo6W2ugWkMvvDLph91QfTJ9js=
	;
References: <4F5F91F6.2020103@turnkey.bz>
In-Reply-To: <4F5F91F6.2020103@turnkey.bz>
Mime-Version: 1.0 (1.0)
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=us-ascii
Message-Id: <6C709AC8-3C03-4E4D-B978-3939FB49591F@yahoo.com>
Cc: "PostgreSQL \(SQL\)" <pgsql-sql@postgresql.org>
From: David Johnston <polobo@yahoo.com>
Subject: Re: help on a function with exception
Date: Wed, 14 Mar 2012 08:30:25 -0400
To: "M. D." <lists@turnkey.bz>

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

> Hi,
>=20
> 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.
>=20
> My idea was to do a Trigger function and cast to a date and if there's an e=
xception, raise an error.  Below is what I've tried, but it just keeps on Ra=
ising Exception.
>=20
> Could someone please help me? The date I enter is: 2012-10-10 which works f=
ine if I do a:
> select '2012-10-10'::date
>=20
> Thanks
>=20
> --Postgres 9.0
>=20
> CREATE OR REPLACE FUNCTION fnc_check_PO_extra_date()
>  RETURNS trigger AS
> $BODY$
> DECLARE
>    tmp_date date;
> BEGIN
>  IF (TG_OP =3D 'INSERT' OR TG_OP =3D 'UPDATE') THEN
>    IF (NEW.data_value IS NOT NULL or new.data_value =3D '') and NEW.extra_=
id =3D
>        (select extra_id from extra_data where data_type =3D 9
>            and (data_name =3D 'ETA' or data_name =3D 'Adjusted ETA'))
>    THEN
>        tmp_date :=3D 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
>=20
> CREATE TRIGGER trg_check_PO_extra_date
>  BEFORE INSERT OR UPDATE
>  ON extra_values
>  FOR EACH ROW
>  EXECUTE PROCEDURE fnc_check_PO_extra_date();
>=20
>=20

You are suppressing the original exception so figuring out what is wrong is v=
ery difficult.

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

David J.