rewriting values with before trigger

From: Robert Treat <xzilla(at)users(dot)sourceforge(dot)net>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>, pgsql-sql(at)postgresql(dot)org
Subject: rewriting values with before trigger
Date: 2003-04-24 16:25:44
Message-ID: 1051201544.4753.522.camel@camel
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-sql

Is there a way to use triggers to rewrite data before determining if the
data for that field is valid?

postgres=# create table foo (
postgres(# bar timestamptz
postgres(# );
CREATE
postgres=# create or replace function test() returns opaque as '
postgres'# begin
postgres'# NEW.bar := NULL;
postgres'# return NEW;
postgres'# end;
postgres'# ' language 'plpgsql';
CREATE
postgres=# create trigger baz before insert on foo for each row execute
procedure test();
CREATE
postgres=# insert into foo values (now());
INSERT 411474706 1
postgres=# select * from foo;
bar
-----

(1 row)

postgres=# insert into foo values ('');
ERROR: Bad timestamp external representation ''

Is there anyway to do be able to change the '' into NULL before data
validity is checked with a trigger?

(FWIW this is 7.2, if this would actually work in 7.3 please lmk)

TIA,
Robert Treat

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Peter Eisentraut 2003-04-24 16:38:39 Re: default locale considered harmful? (was Re: Using
Previous Message Tom Lane 2003-04-24 15:42:42 Re: [NOVICE] Postgres Remote Syslog

Browse pgsql-sql by date

  From Date Subject
Next Message Josh Berkus 2003-04-24 16:39:54 Re: [SQL] rewriting values with before trigger
Previous Message Andreas Pflug 2003-04-24 11:41:30 Statement triggers 7.4 NEW/OLD