From: David Johnston <polobo@yahoo.com>
To: M. D. <lists@turnkey.bz>
Cc: PostgreSQL \(SQL\) <pgsql-sql@postgresql.org>
Subject: Re: help on a function with exception
Date: Wed, 14 Mar 2012 08:30:25 -0400
Message-ID: <6C709AC8-3C03-4E4D-B978-3939FB49591F@yahoo.com> (raw)
In-Reply-To: <4F5F91F6.2020103@turnkey.bz>
References: <4F5F91F6.2020103@turnkey.bz>
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 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: polobo@yahoo.com, lists@turnkey.bz
Subject: Re: help on a function with exception
In-Reply-To: <6C709AC8-3C03-4E4D-B978-3939FB49591F@yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox