On Jul 31, 2006, at 10:59 AM, Aaron Bono wrote:
On 7/31/06, John DeSoi <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
