CREATE TABLE foo (
	name TEXT,
	type CHAR(1),
	when_added TIMESTAMP DEFAULT 'now'
);

CREATE VIEW mytype AS 
	SELECT name, when_added FROM foo WHERE type = 'M';

CREATE RULE mytype_insert AS
	ON INSERT TO mytype DO INSTEAD
	INSERT INTO foo (name, type) VALUES (NEW.name, 'M');

db=# insert into foo (name, type) VALUES ('n1', 'M');
INSERT 414488 1
db=# insert into mytype (name) VALUES ('n2');
INSERT 414489 1
db=# select * from foo;
 name | type |       when_added
------+------+------------------------
 n1   | M    | 2000-06-15 09:53:44-04
 n2   | M    | 2000-06-15 09:52:27-04
(2 rows)

Inserting directly into foo sets when_added to the current time.
Inserting through the view sets it to what looks like the time of
view creation.

-- 

Mark Hollomon
mhh@nortelnetworks.com
ESN 451-9008 (302)454-9008

Thu, 15 Jun 2000 10:05:10 -0400
"Mark Hollomon" <mhh@nortelnetworks.com>
Bug with views and defaults
"Mark Hollomon" <mhh@nortelnetworks.com> writes:
> CREATE TABLE foo (
> 	name TEXT,
> 	type CHAR(1),
> 	when_added TIMESTAMP DEFAULT 'now'
> );

> CREATE VIEW mytype AS 
> 	SELECT name, when_added FROM foo WHERE type = 'M';

> CREATE RULE mytype_insert AS
> 	ON INSERT TO mytype DO INSTEAD
> 	INSERT INTO foo (name, type) VALUES (NEW.name, 'M');

> Inserting directly into foo sets when_added to the current time.
> Inserting through the view sets it to what looks like the time of
> view creation.

This is a known and not readily fixable problem.  It's far safer
to write the default for a timestamp column as now(), rather than
relying on a string literal not getting coerced to timestamp form
too soon.  See
http://www.postgresql.org/mhonarc/pgsql-hackers/1999-10/msg00036.html

BTW, Bruce: it probably would be wise to have the FAQ's item 4.22
recommend now() and nothing else.  'now' has nothing much to recommend
it and there are still pitfalls like this one.

			regards, tom lane

Fri, 16 Jun 2000 19:43:10 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Bug with views and defaults 
> This is a known and not readily fixable problem.  It's far safer
> to write the default for a timestamp column as now(), rather than
> relying on a string literal not getting coerced to timestamp form
> too soon.  See
> http://www.postgresql.org/mhonarc/pgsql-hackers/1999-10/msg00036.html
> 
> BTW, Bruce: it probably would be wise to have the FAQ's item 4.22
> recommend now() and nothing else.  'now' has nothing much to recommend
> it and there are still pitfalls like this one.
> 
> 			regards, tom lane
> 

TODO updated.


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Fri, 16 Jun 2000 20:06:49 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Bug with views and defaults