Re: Insert a default timestamp when nothing given - Mailing list pgsql-general

From Jim Buttafuoco
Subject Re: Insert a default timestamp when nothing given
Date
Msg-id 20060119141451.M96783@contactbda.com
Whole thread Raw
In response to Re: Insert a default timestamp when nothing given  (" Martin Pohl" <Nilpherd@gmx.net>)
List pgsql-general
Change the column type in the view to text, then in the insert/update rule, if the value is '' insert null or what
ever,
else insert the date (as text) into the real date column (as a date)

Jim


---------- Original Message -----------
From: "  Martin Pohl" <Nilpherd@gmx.net>
To: Doug McNaught <doug@mcnaught.org>
Cc: pgsql-general@postgresql.org
Sent: Thu, 19 Jan 2006 14:43:26 +0100 (MET)
Subject: Re: [GENERAL] Insert a default timestamp when nothing given

> Hi,
>
> > > Not directly. I suppose you could create a view that converted the
> > > value to the right date on insert.
> > I think a trigger might make more sense.
>
> That was a very good idea! I tought it would solve my problem. Unfortunately
> it didn't: I still get the "invalid syntax" error (I ensured that the
> trigger worked by using other values). Apparently the syntax check is done,
> before the trigger is called:
> ----
> create or replace function test() returns trigger as '
> begin
>
>     if NEW.datum = '''' THEN
>         NEW.datum := ''01.01.1900'';
>     end if;
>     return NEW;
> end;
> ' language plpgsql;
>
> create trigger test before insert or update on foo
>     for each row execute procedure test();
> ----
>
> Adding a default value will also not work, since the given date is not a
> correct timestampz when inserting. So the default value doesn't help.
>
> I know that inserting '' is wrong in the first place, and that Postgre works
> correctly at this point. But I can't help it - the application I have to
> port does it and I can't change it. Therefore I need a smart workaround for
> a sloppy programming in the application and a sloppy MS SQL.
> (This is not meant rude in any way, it's just the situation I was given in a
> task)
>
> Does anyone have any other suggestions or ideas?
>
> --
> 10 GB Mailbox, 100 FreeSMS/Monat http://www.gmx.net/de/go/topmail
> +++ GMX - die erste Adresse für Mail, Message, More +++
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org
------- End of Original Message -------


pgsql-general by date:

Previous
From: Shane Wright
Date:
Subject: Re: mount -o async - is it safe?
Next
From: Martijn van Oosterhout
Date:
Subject: Re: Insert a default timestamp when nothing given