Skip site navigation (1) Skip section navigation (2)

Re: Triggers using PL/pgSQL

From: "Aaron Bono" <postgresql(at)aranya(dot)com>
To: "John DeSoi" <desoi(at)pgedit(dot)com>
Cc: "Thusitha Kodikara" <kthusi(at)yahoo(dot)com>, pgsql-sql(at)postgresql(dot)org
Subject: Re: Triggers using PL/pgSQL
Date: 2006-07-31 14:59:22
Message-ID: bf05e51c0607310759m64d39c45n73aa6060c871b46f@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-phppgsql-sql
On 7/31/06, John DeSoi <desoi(at)pgedit(dot)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
==================================================================

In response to

Responses

pgsql-php by date

Next:From: Ken HillDate: 2006-07-31 15:47:50
Subject: Re: Triggers using PL/pgSQL
Previous:From: John DeSoiDate: 2006-07-31 12:17:19
Subject: Re: Triggers using PL/pgSQL

pgsql-sql by date

Next:From: Harald FuchsDate: 2006-07-31 15:30:14
Subject: Re: DBD::Pg ... how would I format this prepare?
Previous:From: John DeSoiDate: 2006-07-31 12:17:19
Subject: Re: Triggers using PL/pgSQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group