Re: Triggers using PL/pgSQL

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