Re: Triggers using PL/pgSQL

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