This is what I do. For many tables, I also add a mod_time column with
On Feb 25, 2008, at 8:19 PM, Gregory Williamson wrote:
>
> Perhaps, make the column so it has a default value of now, land then
> insert without specifying the timestamp column so it gets the
> default value:
>
> create table foo (q_i_time timestamp with time zone not null default
> now(), someval int);
>
an ON UPDATE trigger similar to the one previously posted. Even when
not needed for the table itself, in development, it can be very handy
to compare dodgy inserts/updates with commit logs when bug hunting...
I know you asked for something more like a create timestamp, but if you also want a modify timestamp, that would definitely need a trigger:
CREATE OR REPLACE FUNCTION "public"."modify_date_stamp_fn" () RETURNS SETOF opaque AS
'
BEGIN
-- if a trigger insert or update operation occurs
IF TG_OP = ''INSERT'' OR TG_OP = ''UPDATE'' THEN
-- assigns the current timestamp
-- into the modify date and time column
NEW.modify_dt := now();
-- displays the new row on an insert/update
RETURN NEW;
END IF;
END;
'
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
CREATE TRIGGER "mytable_modify_dt_tr" BEFORE UPDATE
ON "public"."mytable" FOR EACH ROW
EXECUTE PROCEDURE "public"."modify_date_stamp_fn"();
This function is very generic and can be used for any and all of your tables so you don't have to create a function for each table as long as the column name is the same.
-Aaron
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
http://codeelixir.com
==================================================================
