Re: Timestamp of insertion of the row.

On Wed, Jun 11, 2003 at 02:42:42PM +0530, Anagha Joshi wrote:
> Hi,
> Is there any way to know data & time when the row is inserted into a
> table?

Yes. Easy answer: use a column of type 'timestamp default now()'. Whenever
row is inserted with the value for that columns unspecified, it will take on
the current timestamp. Harder answer: write a function to update the
timestamp columns and run before update or insert as a trigger on the table
in question. Here is working example:

CREATE TABLE "example" (
"id" integer DEFAULT nextval('"example_id_seq"'::text) NOT NULL,
"descr" text,
"mod" timestamp with time zone DEFAULT now(),
"last_user" text,
Constraint "example_pkey" Primary Key ("id")
);

CREATE OR REPLACE FUNCTION "update_example_timestamp" () RETURNS opaque AS '
BEGIN
-- Remember who last changed the row and when
NEW.mod := ''now'';
NEW.last_user := current_user;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER "example_on_update_set_timestamp" BEFORE INSERT OR UPDATE ON "example" FOR EACH ROW EXECUTE PROCEDURE "update_example_timestamp" ();

This also logs the last user to modify the row. This system provides only
rudimentary accountability; a more rigorous solution would be to log all
inserts and updates to a row in another table example_log with columns for
example id, timestamp, and user.

PS. On most public lists, HTML e-mail is considered improper. It will also
cause people who filter HTML e-mail as spam to likely not see your messages.
I recommend sending plain text e-mail only to public mailing lists.

--
Henry House
The attached file is a digital signature. See <http://romana.hajhouse.org/pgp>
for information. My OpenPGP key: <http://romana.hajhouse.org/hajhouse.asc>.