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>.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Reply via email to