Skip site navigation (1) Skip section navigation (2)

Re: help on a function with exception

From: David Johnston <polobo(at)yahoo(dot)com>
To: "M(dot) D(dot)" <lists(at)turnkey(dot)bz>
Cc: "PostgreSQL \(SQL\)" <pgsql-sql(at)postgresql(dot)org>
Subject: Re: help on a function with exception
Date: 2012-03-14 12:30:25
Message-ID: 6C709AC8-3C03-4E4D-B978-3939FB49591F@yahoo.com (view raw or flat)
Thread:
Lists: pgsql-sql
On Mar 13, 2012, at 14:29, "M. D." <lists(at)turnkey(dot)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.


In response to

pgsql-sql by date

Next:From: Lee HachadoorianDate: 2012-03-15 16:16:14
Subject: COPY without quoting
Previous:From: David JohnstonDate: 2012-03-13 18:34:57
Subject: Re: Invalid syntax for integer

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group