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 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
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
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
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
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
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 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
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
>
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 > > >
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 >
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.
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. >