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
