Re: [SQL] rewriting values with before trigger

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