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

Re: Triggers using PL/pgSQL

From: John DeSoi <desoi(at)pgedit(dot)com>
To: Aaron Bono <postgresql(at)aranya(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 15:53:46
Message-ID: D1B84C5C-99EA-4DA3-B5EC-22D03CFC1CB8@pgedit.com (view raw or flat)
Thread:
Lists: pgsql-phppgsql-sql
On Jul 31, 2006, at 10:59 AM, Aaron Bono wrote:

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

I did not test with older versions, but it seems to work fine with 8.1:


CREATE OR REPLACE FUNCTION my_table_history_fn () returns trigger as
'
BEGIN
     -- if a trigger insert or update operation occurs
     IF TG_OP = ''INSERT'' OR TG_OP = ''UPDATE'' THEN
     	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;


insert into my_table values (1, 'test1');
insert into my_table values (2, 'test2');
update my_table set my_value = 'test3' where my_table_id = 1;
select * from my_table_history;

=== psql 9 ===
my_table_id | my_value |         create_dt
-------------+----------+----------------------------
            1 | test1    | 2006-07-31 11:47:33.080556
            2 | test2    | 2006-07-31 11:47:48.221009
            1 | test3    | 2006-07-31 11:48:21.029696
(3 rows)




John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


In response to

pgsql-php by date

Next:From: Milen A. RadevDate: 2006-07-31 16:12:30
Subject: Re: Triggers using PL/pgSQL
Previous:From: Ken HillDate: 2006-07-31 15:47:50
Subject: Re: Triggers using PL/pgSQL

pgsql-sql by date

Next:From: Milen A. RadevDate: 2006-07-31 16:12:30
Subject: Re: Triggers using PL/pgSQL
Previous:From: Ken HillDate: 2006-07-31 15:47:50
Subject: Re: Triggers using PL/pgSQL

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