Thread: rewriting values with before trigger
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
Robert, > Is there anyway to do be able to change the '' into NULL before data > validity is checked with a trigger? What about using a RULE instead? -- Josh Berkus Aglio Database Solutions San Francisco
On 24 Apr 2003, Robert Treat wrote:
> Is there a way to use triggers to rewrite data before determining if the
> data for that field is valid?
>
> ...
>
> 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)
Just so you know 7.3 is the same. I've just been doing something similar,
i.e. wanting to get untyped values into a before trigger but then it's
eminently sensible for that to not be possible.
I hadn't really considered rules but then the process I wanted done was way to
complicated for one of those.
Fortunately the design is such that I could just move the code I would have
written in the trigger into the access function that the middle layer is
supposed to use.
--
Nigel J. Andrews
On Thu, 2003-04-24 at 12:39, Josh Berkus wrote: > Robert, > > > Is there anyway to do be able to change the '' into NULL before data > > validity is checked with a trigger? > > What about using a RULE instead? > RULE's follow this same behavior, erroring out if the data is not a valid timestamp. (At least I've not been able to write a rule that doesn't follow this) In fact you actually can't create the rule if you have a where clause that isn't a proper matching datatype. I seem to recall a solution for folks who need '' to represent 0 in int fields was to use triggers or rules, but I guess that really isn't possible :-( Robert Treat
How about putting the data validity and conversion routine in ONE trigger? OR, deliberately name your triggers with alphanumerical prefixes that forces them to fire in a certain order, and makingthe trigger that does the conversion fire first? I have no idea how the transport of a value of '' happens in the parser if it's expecing a NULL/INT value, if it would evenmake it to the trigger stage <shrug>. Robert Treat wrote: > On Thu, 2003-04-24 at 12:39, Josh Berkus wrote: > >>Robert, >> >> >>>Is there anyway to do be able to change the '' into NULL before data >>>validity is checked with a trigger? >> >>What about using a RULE instead? >> > > > RULE's follow this same behavior, erroring out if the data is not a > valid timestamp. (At least I've not been able to write a rule that > doesn't follow this) In fact you actually can't create the rule if you > have a where clause that isn't a proper matching datatype. > > I seem to recall a solution for folks who need '' to represent 0 in int > fields was to use triggers or rules, but I guess that really isn't > possible :-( > > Robert Treat > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html >
On Thu, 2003-04-24 at 15:10, Dennis Gearon wrote: > I have no idea how the transport of a value of '' happens in the parser if it's expecing a NULL/INT value, if it wouldeven make it to the trigger stage <shrug>. > :-) Thats the problem, it doesn't. Robert Treat
Well,
does ''::INT do anything valuable?
change column to a string, convert to INTS on the way out, STRINGS on the way in?
use an external language to make transitions.
Robert Treat wrote:
> On Thu, 2003-04-24 at 15:10, Dennis Gearon wrote:
>
>>I have no idea how the transport of a value of '' happens in the parser if it's expecing a NULL/INT value, if it
wouldeven make it to the trigger stage <shrug>.
>>
>
>
> :-) Thats the problem, it doesn't.
>
> Robert Treat
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
On Thu, 2003-04-24 at 15:35, Dennis Gearon wrote:
> Well,
> does ''::INT do anything valuable?
not in 7.3+, and given my original problem involves timestamps, not in
7.2 either.
> change column to a string, convert to INTS on the way out, STRINGS on the way in?
> use an external language to make transitions.
>
yeah, I've thought of some other crazy ways to get around this, but the
problem is due to a bug in the application side. I was hoping I could
write a quick hack (like adding a rule/trigger) to get around this until
the app was fixed, but I'm not going to make my side a kludge when it
isn't one now.
Robert Treat
I **LIKE** your kind of thinking :-) Don't mess up the data. Don't mess up the schema. It hurts or causes LOTS of labor later on. Robert Treat wrote: > On Thu, 2003-04-24 at 15:35, Dennis Gearon wrote: > >>Well, >> does ''::INT do anything valuable? > > > not in 7.3+, and given my original problem involves timestamps, not in > 7.2 either. > > >> change column to a string, convert to INTS on the way out, STRINGS on the way in? >> use an external language to make transitions. >> > > > yeah, I've thought of some other crazy ways to get around this, but the > problem is due to a bug in the application side. I was hoping I could > write a quick hack (like adding a rule/trigger) to get around this until > the app was fixed, but I'm not going to make my side a kludge when it > isn't one now. > > > Robert Treat > > >
> OR, deliberately name your triggers with alphanumerical prefixes that > forces them to fire in a certain order, and making the trigger that > does the conversion fire first? The SQL 99 standard mandates that triggers fire the order defined, though the docs say pgsql does it in alphabetical order, possibly because that was easier to implement. It is probably better to assume that in any given situation they will fire in a random order and sequence events yourself when necessary. That may mean throwing the '' into NULL logic into every trigger for that table, though there are probably more elegant ways to achieve that result. If God (or Codd?) had meant us to enable triggers to fire in a particular order, there would be a 'firing order nnnn' sequencing parameter in the standard. -- Mike Nolan
I'm by no means an expert in this, but maybe this idea would work in your rule? Write a function that takes text as it's input and returns either a timestamp or NULL and then do something like: return_timestamp(mydate::text) This way, a valid date will be converted to it's text equiv. You can then use the function to look for '' and if true, return NULL and if false cast the text string back into a timestamp or whatever and return it. Of course, I don't know if a function that is set to return a timestamp can even return NULL. That might cause an error by itself. -- Matthew Nuzum www.bearfruit.org cobalt@bearfruit.org > -----Original Message----- > From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql- > owner@postgresql.org] On Behalf Of Robert Treat > Sent: Thursday, April 24, 2003 3:49 PM > To: gearond@cvc.net > Cc: Josh Berkus; pgsql-general@postgresql.org; pgsql-sql@postgresql.org > Subject: Re: [GENERAL] [SQL] rewriting values with before trigger > > On Thu, 2003-04-24 at 15:35, Dennis Gearon wrote: > > Well, > > does ''::INT do anything valuable? > > not in 7.3+, and given my original problem involves timestamps, not > in > 7.2 either. > > > change column to a string, convert to INTS on the way out, STRINGS > on the way in? > > use an external language to make transitions. > > > > yeah, I've thought of some other crazy ways to get around this, but the > problem is due to a bug in the application side. I was hoping I could > write a quick hack (like adding a rule/trigger) to get around this until > the app was fixed, but I'm not going to make my side a kludge when it > isn't one now. > > > Robert Treat > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster
On Thu, 24 Apr 2003, Matthew Nuzum wrote:
> I'm by no means an expert in this, but maybe this idea would work in your
> rule?
>
> Write a function that takes text as it's input and returns either a
> timestamp or NULL and then do something like:
> return_timestamp(mydate::text)
>
> This way, a valid date will be converted to it's text equiv. You can then
> use the function to look for '' and if true, return NULL and if false cast
> the text string back into a timestamp or whatever and return it.
>
> Of course, I don't know if a function that is set to return a timestamp can
> even return NULL. That might cause an error by itself.
No reason why it can't return a null and indeed that's a decent
suggestion. What I was going to (re-)say is based on something I've been doing
recently where the db interface for the application is defined as stored procs
only. That enabled me to simply use functions that took the tables columns as
text and run their magic without compromising the db design by making integer
etc. columns text type.
If Mr Treat can get the app. coders to change
INSERT INTO mytable ....
into
SELECT insert_into_mytable(...)
then he can get around the problem.
>
> --
> Matthew Nuzum
> www.bearfruit.org
> cobalt@bearfruit.org
>
> > -----Original Message-----
> > From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-
> > owner@postgresql.org] On Behalf Of Robert Treat
> > Sent: Thursday, April 24, 2003 3:49 PM
> > To: gearond@cvc.net
> > Cc: Josh Berkus; pgsql-general@postgresql.org; pgsql-sql@postgresql.org
> > Subject: Re: [GENERAL] [SQL] rewriting values with before trigger
> >
> > On Thu, 2003-04-24 at 15:35, Dennis Gearon wrote:
> > > Well,
> > > does ''::INT do anything valuable?
> >
> > not in 7.3+, and given my original problem involves timestamps, not
> > in
> > 7.2 either.
> >
> > > change column to a string, convert to INTS on the way out, STRINGS
> > on the way in?
> > > use an external language to make transitions.
> > >
> >
> > yeah, I've thought of some other crazy ways to get around this, but the
> > problem is due to a bug in the application side. I was hoping I could
> > write a quick hack (like adding a rule/trigger) to get around this until
> > the app was fixed, but I'm not going to make my side a kludge when it
> > isn't one now.
> >
> >
> > Robert Treat
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
--
Nigel J. Andrews
Robert Treat <xzilla@users.sourceforge.net> writes:
> yeah, I've thought of some other crazy ways to get around this, but the
> problem is due to a bug in the application side. I was hoping I could
> write a quick hack (like adding a rule/trigger) to get around this until
> the app was fixed, but I'm not going to make my side a kludge when it
> isn't one now.
The only way I can see to do it is to make a view that has 'text'
datatypes in place of real ones, with an ON INSERT rule that invokes
your conversion functions and then inserts the result to the real table.
Dunno if that's too high on your kluge meter.
regards, tom lane
I don't think this is a bug at all.
Argument creation has to come before the execution
of the function. That in this case the argument is a row type
doesn't matter. It has nothing to do with the content of the
function and everything to do with creating the row NEW.
How can you possibly assign a value to NEW.bar if NEW as
a whole does not exist?
What are you really trying to do here? Maybe setting the
table column's default to NULL will achieve what you want.
elein
On Thursday 24 April 2003 12:49, Robert Treat wrote:
> On Thu, 2003-04-24 at 15:35, Dennis Gearon wrote:
> > Well,
> > does ''::INT do anything valuable?
>
> not in 7.3+, and given my original problem involves timestamps, not in
> 7.2 either.
>
> > change column to a string, convert to INTS on the way out, STRINGS on
> > the way in? use an external language to make transitions.
>
> yeah, I've thought of some other crazy ways to get around this, but the
> problem is due to a bug in the application side. I was hoping I could
> write a quick hack (like adding a rule/trigger) to get around this until
> the app was fixed, but I'm not going to make my side a kludge when it
> isn't one now.
>
>
> Robert Treat
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--
----------------------------------------------------------------------------------------
elein@varlena.com Database Consulting www.varlena.com
I have always depended on the [QA] of strangers.
On Thursday 24 April 2003 08:28 pm, elein wrote: > I don't think this is a bug at all. > Argument creation has to come before the execution > of the function. That in this case the argument is a row type > doesn't matter. It has nothing to do with the content of the > function and everything to do with creating the row NEW. > How can you possibly assign a value to NEW.bar if NEW as > a whole does not exist? > You misunderstood. I don't think it's a bug in postgresql, it's a bug in the application that is hitting against my database. When it doesn't have a value for the timestamp field, it either needs to drop it from the insert statment or convert it to null; not send a '' > What are you really trying to do here? Maybe setting the > table column's default to NULL will achieve what you want. > I have an application that is inserting information into a table periodically. Sometimes it passes in timestamps for certain fields, but sometimes it doesn't have any timestamp information. When it doesn't have information, it should send NULL. Unfortunatly the app writers wrote it to send ''. Default's don't work because the app is trying to post data, it's just not a valid datatype. Luckily in this instance I can tell the app guys they have to fix thier app instead of having to muck up the database. Robert Treat
Robert, > You misunderstood. I don't think it's a bug in postgresql, it's a bug in the > application that is hitting against my database. When it doesn't have a value > for the timestamp field, it either needs to drop it from the insert statment > or convert it to null; not send a '' Incidentally, this sort of problem is why most of my apps are based on "push" data functions. i.e., instead of the client calling: INSERT INTO foo VALUES ( id, bar1, bar2, bar3 ); it calls SELECT df_modify_foo ( id, bar1, bar2, bar3 ); Data-push functions allow me to do a whole array of validation and custom error message return that would be impractical with triggers. It also allows me to build security checks in to the back-end, via: SELECT df_modify_foo ( user_id, session_key, id, bar1, bar2, bar3 ) ... allowing me to check all of the following things: Does the user have a valid session? Does the user have rights to foo? Does the user have a lock on foo? Is this a new foo record, or a modified one? etc. -- -Josh Berkus Aglio Database Solutions San Francisco
It's a lot of work to write the functions, but it is the best way to keep the db intact. Josh Berkus wrote: > Robert, > > >>You misunderstood. I don't think it's a bug in postgresql, it's a bug in > > the > >>application that is hitting against my database. When it doesn't have a > > value > >>for the timestamp field, it either needs to drop it from the insert statment >>or convert it to null; not send a '' > > > Incidentally, this sort of problem is why most of my apps are based on "push" > data functions. i.e., instead of the client calling: > INSERT INTO foo VALUES ( id, bar1, bar2, bar3 ); > it calls > SELECT df_modify_foo ( id, bar1, bar2, bar3 ); > > Data-push functions allow me to do a whole array of validation and custom > error message return that would be impractical with triggers. It also > allows me to build security checks in to the back-end, via: > > SELECT df_modify_foo ( user_id, session_key, id, bar1, bar2, bar3 ) > > ... allowing me to check all of the following things: > Does the user have a valid session? > Does the user have rights to foo? > Does the user have a lock on foo? > Is this a new foo record, or a modified one? > etc. >
OK. I understand what you mean. I was really focussing
on the engine handling rather than the application behaviour.
elein
On Thursday 24 April 2003 21:03, Robert Treat wrote:
> On Thursday 24 April 2003 08:28 pm, elein wrote:
> > I don't think this is a bug at all.
> > Argument creation has to come before the execution
> > of the function. That in this case the argument is a row type
> > doesn't matter. It has nothing to do with the content of the
> > function and everything to do with creating the row NEW.
> > How can you possibly assign a value to NEW.bar if NEW as
> > a whole does not exist?
>
> You misunderstood. I don't think it's a bug in postgresql, it's a bug in
> the application that is hitting against my database. When it doesn't have a
> value for the timestamp field, it either needs to drop it from the insert
> statment or convert it to null; not send a ''
>
> > What are you really trying to do here? Maybe setting the
> > table column's default to NULL will achieve what you want.
>
> I have an application that is inserting information into a table
> periodically. Sometimes it passes in timestamps for certain fields, but
> sometimes it doesn't have any timestamp information. When it doesn't have
> information, it should send NULL. Unfortunatly the app writers wrote it to
> send ''. Default's don't work because the app is trying to post data, it's
> just not a valid datatype. Luckily in this instance I can tell the app
> guys they have to fix thier app instead of having to muck up the database.
>
> Robert Treat
--
----------------------------------------------------------------------------------------
elein@varlena.com Database Consulting www.varlena.com
I have always depended on the [QA] of strangers.