From: lucas@presserv.org
To: CHRIS HOOVER <CHRIS.HOOVER@companiongroup.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Record Log Trigger
Date: Tue, 03 May 2005 09:14:52 -0300
Message-ID: <20050503091452.v0tk3a2hcnqc04g8@www.presserv.org> (raw)
In-Reply-To: <NY696b37-43336dd8@companiongroup.com>
References: <NY696b37-43336dd8@companiongroup.com>
Well...
Right, I will use to_timestamp() function instead of now() function.
But, what is the performance for those Triggers??? Considering that all tables
will have this Trigger and will check for each update or insert.
Thanks
Quoting CHRIS HOOVER <CHRIS.HOOVER@companiongroup.com>:
> One change you might want to look at is not using the now() function.
> According to the docs, the now() function always returns the start of the
> transaction time. So, if your code is using transaction blocks, the time may
> not be what you are expecting.
>
> This is what I had do to in my trigger to get the current clock time:
> to_char(to_timestamp(timeofday(),\'Dy Mon DD HH24:MI:SS.US YYYY\')
>
>
> HTH,
>
> Chris
> ------------------( Forwarded letter 1 follows )---------------------
> Date: Mon, 02 May 2005 16:10:46 -0300
> To: pgsql-sql@postgresql.org.comp
> From: lucas@presserv.org.comp
> Sender: pgsql-sql-owner+m21335@postgresql.org.comp
> Subject: [SQL] Record Log Trigger
>
> Hi all,
> I am building a database in postgresql and I made a function that returns the
> system time and the current user... like this:
>
> CREATE OR REPLACE FUNCTION generate_idx() returns text as
> $$
> select to_char(now(),'YYYYMMDDHHMISSUSTZ')||CURRENT_USER;
> $$ language 'SQL';
>
> CREATE OR REPLACE FUNCTION TG_idxm() RETURNS trigger AS
> $$
> BEGIN
> NEW.idxm = generate_idx();
> RETURN NEW;
> END;
> $$ LANGUAGE plpgsql;
>
> And my all tables have the "idxm" field, its something like a log for the
> record, to know Who and When the record have changed. I.e:
>
> CREATE TABLE products(
> id serial primary key,
> description varchar(50),
> ...
> idxm varchar(100)
> );
> CREATE TRIGGER TG_products_idxm BEFORE INSERT or UPDATE on products
> FOR EACH ROW
> EXECUTE PROCEDURE TG_idxm();
>
> Okay, it runs fine... but my question is:
> Is it right??? In the future (when the database will be bigger with many of
> millions records) this functions for each table will depreceate my database
> performance???
> Is there any other way to build it???
>
> Thank you
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: lucas@presserv.org, CHRIS.HOOVER@companiongroup.com
Subject: Re: Record Log Trigger
In-Reply-To: <20050503091452.v0tk3a2hcnqc04g8@www.presserv.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox