In-Reply-To: <bf05e51c0607310759m64d39c45n73aa6060c871b46f@mail.gmail.com>
References: <20060731041644.32247.qmail@web56610.mail.re3.yahoo.com>
	<bf05e51c0607302152k2801caflc9040d99af7b2816@mail.gmail.com>
	<7F7548C8-249E-44E2-8205-6C055671DA19@pgedit.com>
	<bf05e51c0607310759m64d39c45n73aa6060c871b46f@mail.gmail.com>
Mime-Version: 1.0 (Apple Message framework v752.2)
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
Message-Id: <D1B84C5C-99EA-4DA3-B5EC-22D03CFC1CB8@pgedit.com>
Cc: "Thusitha Kodikara" <kthusi@yahoo.com>,
 pgsql-sql@postgresql.org
Content-Transfer-Encoding: 7bit
From: John DeSoi <desoi@pgedit.com>
Subject: Re: Triggers using PL/pgSQL
Date: Mon, 31 Jul 2006 11:53:46 -0400
To: Aaron Bono <postgresql@aranya.com>


On Jul 31, 2006, at 10:59 AM, Aaron Bono wrote:

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

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