pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

help on a function with exception
2+ messages / 2 participants
[nested] [flat]

* help on a function with exception
@ 2012-03-13 18:29 M. D. <lists@turnkey.bz>
  2012-03-14 12:30 ` Re: help on a function with exception David Johnston <polobo@yahoo.com>
  0 siblings, 1 reply; 2+ messages in thread

From: M. D. @ 2012-03-13 18:29 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: help on a function with exception
  2012-03-13 18:29 help on a function with exception M. D. <lists@turnkey.bz>
@ 2012-03-14 12:30 ` David Johnston <polobo@yahoo.com>
  0 siblings, 0 replies; 2+ messages in thread

From: David Johnston @ 2012-03-14 12:30 UTC (permalink / raw)
  To: M. D. <lists@turnkey.bz>; +Cc: pgsql-sql

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.




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2012-03-14 12:30 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-03-13 18:29 help on a function with exception M. D. <lists@turnkey.bz>
2012-03-14 12:30 ` David Johnston <polobo@yahoo.com>

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