pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedrewriting values with before trigger
18+ messages / 8 participants
[nested] [flat]
* rewriting values with before trigger
@ 2003-04-24 16:25 Robert Treat <xzilla@users.sourceforge.net>
2003-04-24 16:39 ` Re: [SQL] rewriting values with before trigger Josh Berkus <josh@agliodbs.com>
2003-04-24 16:46 ` Re: rewriting values with before trigger Nigel J. Andrews <nandrews@investsystems.co.uk>
0 siblings, 2 replies; 18+ messages in thread
From: Robert Treat @ 2003-04-24 16:25 UTC (permalink / raw)
To: pgsql-general@postgresql.org <pgsql-general@postgresql.org>; 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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: [SQL] rewriting values with before trigger
2003-04-24 16:25 rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
@ 2003-04-24 16:39 ` Josh Berkus <josh@agliodbs.com>
2003-04-24 18:38 ` Re: [SQL] rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
1 sibling, 1 reply; 18+ messages in thread
From: Josh Berkus @ 2003-04-24 16:39 UTC (permalink / raw)
To: Robert Treat <xzilla@users.sourceforge.net>; pgsql-general@postgresql.org <pgsql-general@postgresql.org>; pgsql-sql
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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: [SQL] rewriting values with before trigger
2003-04-24 16:25 rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
2003-04-24 16:39 ` Re: [SQL] rewriting values with before trigger Josh Berkus <josh@agliodbs.com>
@ 2003-04-24 18:38 ` Robert Treat <xzilla@users.sourceforge.net>
2003-04-24 19:10 ` Re: [SQL] rewriting values with before trigger Dennis Gearon <gearond@cvc.net>
0 siblings, 1 reply; 18+ messages in thread
From: Robert Treat @ 2003-04-24 18:38 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org>; pgsql-sql
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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: [SQL] rewriting values with before trigger
2003-04-24 16:25 rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
2003-04-24 16:39 ` Re: [SQL] rewriting values with before trigger Josh Berkus <josh@agliodbs.com>
2003-04-24 18:38 ` Re: [SQL] rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
@ 2003-04-24 19:10 ` Dennis Gearon <gearond@cvc.net>
2003-04-24 19:22 ` Re: [SQL] rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
2003-04-24 21:18 ` Re: [SQL] rewriting values with before trigger nolan@celery.tssi.com
0 siblings, 2 replies; 18+ messages in thread
From: Dennis Gearon @ 2003-04-24 19:10 UTC (permalink / raw)
To: Robert Treat <xzilla@users.sourceforge.net>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-general@postgresql.org <pgsql-general@postgresql.org>; pgsql-sql
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 making the 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 even make 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
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: [SQL] rewriting values with before trigger
2003-04-24 16:25 rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
2003-04-24 16:39 ` Re: [SQL] rewriting values with before trigger Josh Berkus <josh@agliodbs.com>
2003-04-24 18:38 ` Re: [SQL] rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
2003-04-24 19:10 ` Re: [SQL] rewriting values with before trigger Dennis Gearon <gearond@cvc.net>
@ 2003-04-24 19:22 ` Robert Treat <xzilla@users.sourceforge.net>
2003-04-24 19:35 ` Re: [SQL] rewriting values with before trigger Dennis Gearon <gearond@cvc.net>
1 sibling, 1 reply; 18+ messages in thread
From: Robert Treat @ 2003-04-24 19:22 UTC (permalink / raw)
To: gearond@cvc.net; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-general@postgresql.org <pgsql-general@postgresql.org>; pgsql-sql
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 would even make it to the trigger stage <shrug>.
>
:-) Thats the problem, it doesn't.
Robert Treat
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: [SQL] rewriting values with before trigger
2003-04-24 16:25 rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
2003-04-24 16:39 ` Re: [SQL] rewriting values with before trigger Josh Berkus <josh@agliodbs.com>
2003-04-24 18:38 ` Re: [SQL] rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
2003-04-24 19:10 ` Re: [SQL] rewriting values with before trigger Dennis Gearon <gearond@cvc.net>
2003-04-24 19:22 ` Re: [SQL] rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
@ 2003-04-24 19:35 ` Dennis Gearon <gearond@cvc.net>
2003-04-24 19:49 ` Re: [SQL] rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
0 siblings, 1 reply; 18+ messages in thread
From: Dennis Gearon @ 2003-04-24 19:35 UTC (permalink / raw)
To: Robert Treat <xzilla@users.sourceforge.net>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-general@postgresql.org <pgsql-general@postgresql.org>; pgsql-sql
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 would even 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
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: [SQL] rewriting values with before trigger
2003-04-24 16:25 rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
2003-04-24 16:39 ` Re: [SQL] rewriting values with before trigger Josh Berkus <josh@agliodbs.com>
2003-04-24 18:38 ` Re: [SQL] rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
2003-04-24 19:10 ` Re: [SQL] rewriting values with before trigger Dennis Gearon <gearond@cvc.net>
2003-04-24 19:22 ` Re: [SQL] rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
2003-04-24 19:35 ` Re: [SQL] rewriting values with before trigger Dennis Gearon <gearond@cvc.net>
@ 2003-04-24 19:49 ` Robert Treat <xzilla@users.sourceforge.net>
2003-04-24 20:35 ` Re: [SQL] rewriting values with before trigger Dennis Gearon <gearond@cvc.net>
2003-04-24 21:31 ` Re: [SQL] rewriting values with before trigger Matthew Nuzum <cobalt@bearfruit.org>
2003-04-24 22:02 ` Re: [SQL] rewriting values with before trigger Tom Lane <tgl@sss.pgh.pa.us>
2003-04-25 00:28 ` Re: [SQL] rewriting values with before trigger elein <elein@sbcglobal.net>
0 siblings, 4 replies; 18+ messages in thread
From: Robert Treat @ 2003-04-24 19:49 UTC (permalink / raw)
To: gearond@cvc.net; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-general@postgresql.org <pgsql-general@postgresql.org>; pgsql-sql
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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: [SQL] rewriting values with before trigger
2003-04-24 16:25 rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
2003-04-24 16:39 ` Re: [SQL] rewriting values with before trigger Josh Berkus <josh@agliodbs.com>
2003-04-24 18:38 ` Re: [SQL] rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
2003-04-24 19:10 ` Re: [SQL] rewriting values with before trigger Dennis Gearon <gearond@cvc.net>
2003-04-24 19:22 ` Re: [SQL] rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
2003-04-24 19:35 ` Re: [SQL] rewriting values with before trigger Dennis Gearon <gearond@cvc.net>
2003-04-24 19:49 ` Re: [SQL] rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
@ 2003-04-24 20:35 ` Dennis Gearon <gearond@cvc.net>
3 siblings, 0 replies; 18+ messages in thread
From: Dennis Gearon @ 2003-04-24 20:35 UTC (permalink / raw)
To: Robert Treat <xzilla@users.sourceforge.net>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-general@postgresql.org <pgsql-general@postgresql.org>; pgsql-sql
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
>
>
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: [SQL] rewriting values with before trigger
2003-04-24 16:25 rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
2003-04-24 16:39 ` Re: [SQL] rewriting values with before trigger Josh Berkus <josh@agliodbs.com>
2003-04-24 18:38 ` Re: [SQL] rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
2003-04-24 19:10 ` Re: [SQL] rewriting values with before trigger Dennis Gearon <gearond@cvc.net>
2003-04-24 19:22 ` Re: [SQL] rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
2003-04-24 19:35 ` Re: [SQL] rewriting values with before trigger Dennis Gearon <gearond@cvc.net>
2003-04-24 19:49 ` Re: [SQL] rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
@ 2003-04-24 21:31 ` Matthew Nuzum <cobalt@bearfruit.org>
2003-04-24 21:53 ` Re: [SQL] rewriting values with before trigger Nigel J. Andrews <nandrews@investsystems.co.uk>
3 siblings, 1 reply; 18+ messages in thread
From: Matthew Nuzum @ 2003-04-24 21:31 UTC (permalink / raw)
To: 'Robert Treat' <xzilla@users.sourceforge.net>; +Cc: 'Josh Berkus' <josh@agliodbs.com>; pgsql-general@postgresql.org; gearond@cvc.net
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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: [SQL] rewriting values with before trigger
2003-04-24 16:25 rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
2003-04-24 16:39 ` Re: [SQL] rewriting values with before trigger Josh Berkus <josh@agliodbs.com>
2003-04-24 18:38 ` Re: [SQL] rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
2003-04-24 19:10 ` Re: [SQL] rewriting values with before trigger Dennis Gearon <gearond@cvc.net>
2003-04-24 19:22 ` Re: [SQL] rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
2003-04-24 19:35 ` Re: [SQL] rewriting values with before trigger Dennis Gearon <gearond@cvc.net>
2003-04-24 19:49 ` Re: [SQL] rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
2003-04-24 21:31 ` Re: [SQL] rewriting values with before trigger Matthew Nuzum <cobalt@bearfruit.org>
@ 2003-04-24 21:53 ` Nigel J. Andrews <nandrews@investsystems.co.uk>
0 siblings, 0 replies; 18+ messages in thread
From: Nigel J. Andrews @ 2003-04-24 21:53 UTC (permalink / raw)
To: Matthew Nuzum <cobalt@bearfruit.org>; +Cc: Robert Treat <xzilla@users.sourceforge.net>; Josh Berkus <josh@agliodbs.com>; pgsql-general@postgresql.org; gearond@cvc.net
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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: [SQL] rewriting values with before trigger
2003-04-24 16:25 rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
2003-04-24 16:39 ` Re: [SQL] rewriting values with before trigger Josh Berkus <josh@agliodbs.com>
2003-04-24 18:38 ` Re: [SQL] rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
2003-04-24 19:10 ` Re: [SQL] rewriting values with before trigger Dennis Gearon <gearond@cvc.net>
2003-04-24 19:22 ` Re: [SQL] rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
2003-04-24 19:35 ` Re: [SQL] rewriting values with before trigger Dennis Gearon <gearond@cvc.net>
2003-04-24 19:49 ` Re: [SQL] rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
@ 2003-04-24 22:02 ` Tom Lane <tgl@sss.pgh.pa.us>
3 siblings, 0 replies; 18+ messages in thread
From: Tom Lane @ 2003-04-24 22:02 UTC (permalink / raw)
To: Robert Treat <xzilla@users.sourceforge.net>; +Cc: gearond@cvc.net, Josh Berkus <josh@agliodbs.com>; pgsql-general@postgresql.org <pgsql-general@postgresql.org>; pgsql-sql
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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: [SQL] rewriting values with before trigger
2003-04-24 16:25 rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
2003-04-24 16:39 ` Re: [SQL] rewriting values with before trigger Josh Berkus <josh@agliodbs.com>
2003-04-24 18:38 ` Re: [SQL] rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
2003-04-24 19:10 ` Re: [SQL] rewriting values with before trigger Dennis Gearon <gearond@cvc.net>
2003-04-24 19:22 ` Re: [SQL] rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
2003-04-24 19:35 ` Re: [SQL] rewriting values with before trigger Dennis Gearon <gearond@cvc.net>
2003-04-24 19:49 ` Re: [SQL] rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
@ 2003-04-25 00:28 ` elein <elein@sbcglobal.net>
2003-04-25 04:03 ` Re: [SQL] rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
3 siblings, 1 reply; 18+ messages in thread
From: elein @ 2003-04-25 00:28 UTC (permalink / raw)
To: Robert Treat <xzilla@users.sourceforge.net>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-general@postgresql.org <pgsql-general@postgresql.org>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: [SQL] rewriting values with before trigger
2003-04-24 16:25 rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
2003-04-24 16:39 ` Re: [SQL] rewriting values with before trigger Josh Berkus <josh@agliodbs.com>
2003-04-24 18:38 ` Re: [SQL] rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
2003-04-24 19:10 ` Re: [SQL] rewriting values with before trigger Dennis Gearon <gearond@cvc.net>
2003-04-24 19:22 ` Re: [SQL] rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
2003-04-24 19:35 ` Re: [SQL] rewriting values with before trigger Dennis Gearon <gearond@cvc.net>
2003-04-24 19:49 ` Re: [SQL] rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
2003-04-25 00:28 ` Re: [SQL] rewriting values with before trigger elein <elein@sbcglobal.net>
@ 2003-04-25 04:03 ` Robert Treat <xzilla@users.sourceforge.net>
2003-04-25 17:20 ` Re: [SQL] rewriting values with before trigger Josh Berkus <josh@agliodbs.com>
2003-04-25 18:57 ` Re: [SQL] rewriting values with before trigger elein <elein@sbcglobal.net>
0 siblings, 2 replies; 18+ messages in thread
From: Robert Treat @ 2003-04-25 04:03 UTC (permalink / raw)
To: elein@varlena.com, elein <elein@sbcglobal.net>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-general@postgresql.org <pgsql-general@postgresql.org>; pgsql-sql
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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: [SQL] rewriting values with before trigger
2003-04-24 16:25 rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
2003-04-24 16:39 ` Re: [SQL] rewriting values with before trigger Josh Berkus <josh@agliodbs.com>
2003-04-24 18:38 ` Re: [SQL] rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
2003-04-24 19:10 ` Re: [SQL] rewriting values with before trigger Dennis Gearon <gearond@cvc.net>
2003-04-24 19:22 ` Re: [SQL] rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
2003-04-24 19:35 ` Re: [SQL] rewriting values with before trigger Dennis Gearon <gearond@cvc.net>
2003-04-24 19:49 ` Re: [SQL] rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
2003-04-25 00:28 ` Re: [SQL] rewriting values with before trigger elein <elein@sbcglobal.net>
2003-04-25 04:03 ` Re: [SQL] rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
@ 2003-04-25 17:20 ` Josh Berkus <josh@agliodbs.com>
2003-04-25 17:47 ` Re: [SQL] rewriting values with before trigger Dennis Gearon <gearond@cvc.net>
1 sibling, 1 reply; 18+ messages in thread
From: Josh Berkus @ 2003-04-25 17:20 UTC (permalink / raw)
To: Robert Treat <xzilla@users.sourceforge.net>; elein@varlena.com, elein <elein@sbcglobal.net>; +Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org>; pgsql-sql
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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: [SQL] rewriting values with before trigger
2003-04-24 16:25 rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
2003-04-24 16:39 ` Re: [SQL] rewriting values with before trigger Josh Berkus <josh@agliodbs.com>
2003-04-24 18:38 ` Re: [SQL] rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
2003-04-24 19:10 ` Re: [SQL] rewriting values with before trigger Dennis Gearon <gearond@cvc.net>
2003-04-24 19:22 ` Re: [SQL] rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
2003-04-24 19:35 ` Re: [SQL] rewriting values with before trigger Dennis Gearon <gearond@cvc.net>
2003-04-24 19:49 ` Re: [SQL] rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
2003-04-25 00:28 ` Re: [SQL] rewriting values with before trigger elein <elein@sbcglobal.net>
2003-04-25 04:03 ` Re: [SQL] rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
2003-04-25 17:20 ` Re: [SQL] rewriting values with before trigger Josh Berkus <josh@agliodbs.com>
@ 2003-04-25 17:47 ` Dennis Gearon <gearond@cvc.net>
0 siblings, 0 replies; 18+ messages in thread
From: Dennis Gearon @ 2003-04-25 17:47 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: Robert Treat <xzilla@users.sourceforge.net>; elein@varlena.com, elein <elein@sbcglobal.net>; pgsql-general@postgresql.org <pgsql-general@postgresql.org>; pgsql-sql
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.
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: [SQL] rewriting values with before trigger
2003-04-24 16:25 rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
2003-04-24 16:39 ` Re: [SQL] rewriting values with before trigger Josh Berkus <josh@agliodbs.com>
2003-04-24 18:38 ` Re: [SQL] rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
2003-04-24 19:10 ` Re: [SQL] rewriting values with before trigger Dennis Gearon <gearond@cvc.net>
2003-04-24 19:22 ` Re: [SQL] rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
2003-04-24 19:35 ` Re: [SQL] rewriting values with before trigger Dennis Gearon <gearond@cvc.net>
2003-04-24 19:49 ` Re: [SQL] rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
2003-04-25 00:28 ` Re: [SQL] rewriting values with before trigger elein <elein@sbcglobal.net>
2003-04-25 04:03 ` Re: [SQL] rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
@ 2003-04-25 18:57 ` elein <elein@sbcglobal.net>
1 sibling, 0 replies; 18+ messages in thread
From: elein @ 2003-04-25 18:57 UTC (permalink / raw)
To: Robert Treat <xzilla@users.sourceforge.net>; elein@varlena.com; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-general@postgresql.org <pgsql-general@postgresql.org>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: [SQL] rewriting values with before trigger
2003-04-24 16:25 rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
2003-04-24 16:39 ` Re: [SQL] rewriting values with before trigger Josh Berkus <josh@agliodbs.com>
2003-04-24 18:38 ` Re: [SQL] rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
2003-04-24 19:10 ` Re: [SQL] rewriting values with before trigger Dennis Gearon <gearond@cvc.net>
@ 2003-04-24 21:18 ` nolan@celery.tssi.com
1 sibling, 0 replies; 18+ messages in thread
From: nolan@celery.tssi.com @ 2003-04-24 21:18 UTC (permalink / raw)
To: gearond@cvc.net; +Cc: xzilla@users.sourceforge.net; josh@agliodbs.com; pgsql-general@postgresql.org; pgsql-sql
> 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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: rewriting values with before trigger
2003-04-24 16:25 rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
@ 2003-04-24 16:46 ` Nigel J. Andrews <nandrews@investsystems.co.uk>
1 sibling, 0 replies; 18+ messages in thread
From: Nigel J. Andrews @ 2003-04-24 16:46 UTC (permalink / raw)
To: Robert Treat <xzilla@users.sourceforge.net>; +Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org>; pgsql-sql
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
^ permalink raw reply [nested|flat] 18+ messages in thread
end of thread, other threads:[~2003-04-25 18:57 UTC | newest]
Thread overview: 18+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-04-24 16:25 rewriting values with before trigger Robert Treat <xzilla@users.sourceforge.net>
2003-04-24 16:39 ` Josh Berkus <josh@agliodbs.com>
2003-04-24 18:38 ` Robert Treat <xzilla@users.sourceforge.net>
2003-04-24 19:10 ` Dennis Gearon <gearond@cvc.net>
2003-04-24 19:22 ` Robert Treat <xzilla@users.sourceforge.net>
2003-04-24 19:35 ` Dennis Gearon <gearond@cvc.net>
2003-04-24 19:49 ` Robert Treat <xzilla@users.sourceforge.net>
2003-04-24 20:35 ` Dennis Gearon <gearond@cvc.net>
2003-04-24 21:31 ` Matthew Nuzum <cobalt@bearfruit.org>
2003-04-24 21:53 ` Nigel J. Andrews <nandrews@investsystems.co.uk>
2003-04-24 22:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-04-25 00:28 ` elein <elein@sbcglobal.net>
2003-04-25 04:03 ` Robert Treat <xzilla@users.sourceforge.net>
2003-04-25 17:20 ` Josh Berkus <josh@agliodbs.com>
2003-04-25 17:47 ` Dennis Gearon <gearond@cvc.net>
2003-04-25 18:57 ` elein <elein@sbcglobal.net>
2003-04-24 21:18 ` nolan@celery.tssi.com
2003-04-24 16:46 ` Nigel J. Andrews <nandrews@investsystems.co.uk>
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