From: M. D. <lists@turnkey.bz>
To: PostgreSQL (SQL) <pgsql-sql@postgresql.org>
Subject: help on a function with exception
Date: Tue, 13 Mar 2012 12:29:10 -0600
Message-ID: <4F5F91F6.2020103@turnkey.bz> (raw)
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();
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: lists@turnkey.bz
Subject: Re: help on a function with exception
In-Reply-To: <4F5F91F6.2020103@turnkey.bz>
* 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