From: | John DeSoi <desoi(at)pgedit(dot)com> |
---|---|
To: | Aaron Bono <postgresql(at)aranya(dot)com> |
Cc: | "Thusitha Kodikara" <kthusi(at)yahoo(dot)com>, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Triggers using PL/pgSQL |
Date: | 2006-07-31 15:53:46 |
Message-ID: | D1B84C5C-99EA-4DA3-B5EC-22D03CFC1CB8@pgedit.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-php pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Milen A. Radev | 2006-07-31 16:12:30 | Re: Triggers using PL/pgSQL |
Previous Message | Ken Hill | 2006-07-31 15:47:50 | Re: Triggers using PL/pgSQL |
From | Date | Subject | |
---|---|---|---|
Next Message | Milen A. Radev | 2006-07-31 16:12:30 | Re: Triggers using PL/pgSQL |
Previous Message | Ken Hill | 2006-07-31 15:47:50 | Re: Triggers using PL/pgSQL |