Re: Triggers using PL/pgSQL

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