From: Aaron Bono <postgresql@aranya.com>
To: John DeSoi <desoi@pgedit.com>
Cc: Thusitha Kodikara <kthusi@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Triggers using PL/pgSQL
Date: Mon, 31 Jul 2006 09:59:22 -0500
Message-ID: <bf05e51c0607310759m64d39c45n73aa6060c871b46f@mail.gmail.com> (raw)
In-Reply-To: <7F7548C8-249E-44E2-8205-6C055671DA19@pgedit.com>
References: <20060731041644.32247.qmail@web56610.mail.re3.yahoo.com>
<bf05e51c0607302152k2801caflc9040d99af7b2816@mail.gmail.com>
<7F7548C8-249E-44E2-8205-6C055671DA19@pgedit.com>
On 7/31/06, John DeSoi <desoi@pgedit.com> wrote:
>
> Is it really necessary to build a SQL string and use execute? It
> seems you could just issue the INSERT statement.
I don't think so but there was some discussion a week or two ago about
mixing variables and using execute. I am curious, does anyone know what the
"best" approach is?
Also, I did not address deletions. If you still need to delete from the
table, you will need to get rid of the foreign key on the history table.
You will also need to decide how the history table will reflect the
recording of those deletions.
I usually don't allow deletes on tables (unless absolutely necessary) and
instead add start/end dates to the tables so rows can be marked as removed.
Then I add a view that filters out the inactive rows - all applications use
the views, they do not query the tables directly. This also allows you to
"delete" rows at sometime in the future or make them appear in the future
too.
On Jul 31, 2006, at 12:52 AM, Aaron Bono wrote:
>
> > CREATE OR REPLACE FUNCTION my_table_history_fn () RETURNS SETOF
> > opaque AS
> > '
> > BEGIN
> > -- if a trigger insert or update operation occurs
> > IF TG_OP = ''INSERT'' OR TG_OP = ''UPDATE'' THEN
> > execute
> > ''INSERT INTO my_table_history ( '' ||
> > '' my_table_id, '' ||
> > '' my_value, '' ||
> > '' create_dt '' ||
> > '') VALUES ( '' ||
> > '' '''''' || NEW.my_table_id || '''''', '' ||
> > '' '''''' || NEW.my_value || '''''', '' ||
> > '' now() '' ||
> > '');''
> > ;
> > RETURN NEW;
> > END IF;
> > END;
> > '
> > LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
>
>
>
> John DeSoi, Ph.D.
> http://pgedit.com/
> Power Tools for PostgreSQL
>
>
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
==================================================================
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: postgresql@aranya.com, desoi@pgedit.com, kthusi@yahoo.com
Subject: Re: Triggers using PL/pgSQL
In-Reply-To: <bf05e51c0607310759m64d39c45n73aa6060c871b46f@mail.gmail.com>
* 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