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?
Also, I did not address deletions. If you still need to delete from the table, you will need to get rid of the foreign key on the history table. You will also need to decide how the history table will reflect the recording of those deletions.
I usually don't allow deletes on tables (unless absolutely necessary) and instead add start/end dates to the tables so rows can be marked as removed. Then I add a view that filters out the inactive rows - all applications use the views, they do not query the tables directly. This also allows you to "delete" rows at sometime in the future or make them appear in the future too.
On Jul 31, 2006, at 12:52 AM, Aaron Bono wrote:
> CREATE OR REPLACE FUNCTION my_table_history_fn () RETURNS SETOF
> opaque AS
> '
> BEGIN
> -- if a trigger insert or update operation occurs
> IF TG_OP = ''INSERT'' OR TG_OP = ''UPDATE'' THEN
> execute
> ''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;
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
--
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com==================================================================