pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTriggers using PL/pgSQL
10+ messages / 6 participants
[nested] [flat]
* Triggers using PL/pgSQL
@ 2006-07-31 04:16 Thusitha Kodikara <kthusi@yahoo.com>
0 siblings, 3 replies; 10+ messages in thread
From: Thusitha Kodikara @ 2006-07-31 04:16 UTC (permalink / raw)
To: pgsql-sql
Hello,
I am interested in developing some triggers to keep track of records that are changed (possibly the changes of one or more specific columns). In addition to keeping the new values, I also need to keep the old values (may be on a separate table).
Though I have done similar things in other RDBMs using SQL, I find doing this in Postgres, a little bit complicated - may be because it needs to be done through a separate procedural language and through a separate function. The Postgres documentation also didn't provide much help ( the examples in C). I was mainly looking for example showing how to refer 'OLD' and 'NEW' rows using PL/pgSQL.
Can someone please direct me to some such examples?
Thanks and regards,
-Thusitha
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Triggers using PL/pgSQL
@ 2006-07-31 04:52 Aaron Bono <postgresql@aranya.com>
parent: Thusitha Kodikara <kthusi@yahoo.com>
2 siblings, 2 replies; 10+ messages in thread
From: Aaron Bono @ 2006-07-31 04:52 UTC (permalink / raw)
To: Thusitha Kodikara <kthusi@yahoo.com>; +Cc: pgsql-sql
On 7/30/06, Thusitha Kodikara <kthusi@yahoo.com> wrote:
>
> Hello,
>
> I am interested in developing some triggers to keep track of records that
> are changed (possibly the changes of one or more specific columns). In
> addition to keeping the new values, I also need to keep the old values (may
> be on a separate table).
>
> Though I have done similar things in other RDBMs using SQL, I find doing
> this in Postgres, a little bit complicated - may be because it needs to be
> done through a separate procedural language and through a separate function.
> The Postgres documentation also didn't provide much help ( the examples in
> C). I was mainly looking for example showing how to refer 'OLD' and 'NEW'
> rows using PL/pgSQL.
>
> Can someone please direct me to some such examples?
>
How about this:
CREATE TABLE my_table (
my_table_id BIGSERIAL NOT NULL,
my_value VARCHAR(100) NOT NULL,
CONSTRAINT my_table_pkey PRIMARY KEY (my_table_id)
);
CREATE TABLE my_table_history (
my_table_id BIGINT NOT NULL,
my_value VARCHAR(100) NOT NULL,
create_dt TIMESTAMP NOT NULL,
CONSTRAINT my_table_history_pkey PRIMARY KEY (my_table_id, create_dt)
);
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;
CREATE TRIGGER my_table_history_tr BEFORE UPDATE OR INSERT
ON my_table FOR EACH ROW
EXECUTE PROCEDURE my_table_history_fn();
I tried it out and it works in version 8.1.
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
==================================================================
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Triggers using PL/pgSQL
@ 2006-07-31 05:30 Thusitha Kodikara <kthusi@yahoo.com>
parent: Aaron Bono <postgresql@aranya.com>
1 sibling, 1 reply; 10+ messages in thread
From: Thusitha Kodikara @ 2006-07-31 05:30 UTC (permalink / raw)
To: Aaron Bono <postgresql@aranya.com>; +Cc: pgsql-sql
Hello,
Thanks a lot Aaron for the very quick and simple example. I just checked it on 7.4.5 also and it worked. I'll be able to continue with my development using the syntax of that example.
Regards,
-Thusitha
Aaron Bono <postgresql@aranya.com> wrote: On 7/30/06, Thusitha Kodikara <kthusi@yahoo.com> wrote: Hello,
I am interested in developing some triggers to keep track of records that are changed (possibly the changes of one or more specific columns). In addition to keeping the new values, I also need to keep the old values (may be on a separate table).
Though I have done similar things in other RDBMs using SQL, I find doing this in Postgres, a little bit complicated - may be because it needs to be done through a separate procedural language and through a separate function. The Postgres documentation also didn't provide much help ( the examples in C). I was mainly looking for example showing how to refer 'OLD' and 'NEW' rows using PL/pgSQL.
Can someone please direct me to some such examples?
How about this:
CREATE TABLE my_table (
my_table_id BIGSERIAL NOT NULL,
my_value VARCHAR(100) NOT NULL,
CONSTRAINT my_table_pkey PRIMARY KEY (my_table_id)
);
CREATE TABLE my_table_history (
my_table_id BIGINT NOT NULL,
my_value VARCHAR(100) NOT NULL,
create_dt TIMESTAMP NOT NULL,
CONSTRAINT my_table_history_pkey PRIMARY KEY (my_table_id, create_dt)
);
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;
CREATE TRIGGER my_table_history_tr BEFORE UPDATE OR INSERT
ON my_table FOR EACH ROW
EXECUTE PROCEDURE my_table_history_fn();
I tried it out and it works in version 8.1.
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
==================================================================
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Triggers using PL/pgSQL
@ 2006-07-31 05:34 Aaron Bono <postgresql@aranya.com>
parent: Thusitha Kodikara <kthusi@yahoo.com>
0 siblings, 0 replies; 10+ messages in thread
From: Aaron Bono @ 2006-07-31 05:34 UTC (permalink / raw)
To: Thusitha Kodikara <kthusi@yahoo.com>; +Cc: pgsql-sql
No problem. I have been meaning to put the same code together for myself
but have been putting it off. It gave me an excuse to stop procrastinating.
On 7/31/06, Thusitha Kodikara <kthusi@yahoo.com> wrote:
>
> Hello,
>
> Thanks a lot Aaron for the very quick and simple example. I just checked
> it on 7.4.5 also and it worked. I'll be able to continue with my
> development using the syntax of that example.
>
*Aaron Bono <postgresql@aranya.com>* wrote:
>
> On 7/30/06, Thusitha Kodikara <kthusi@yahoo.com> wrote:
> >
> > Hello,
> >
> > I am interested in developing some triggers to keep track of records
> > that are changed (possibly the changes of one or more specific columns). In
> > addition to keeping the new values, I also need to keep the old values (may
> > be on a separate table).
> >
> > Though I have done similar things in other RDBMs using SQL, I find
> > doing this in Postgres, a little bit complicated - may be because it needs
> > to be done through a separate procedural language and through a separate
> > function. The Postgres documentation also didn't provide much help ( the
> > examples in C). I was mainly looking for example showing how to refer 'OLD'
> > and 'NEW' rows using PL/pgSQL.
> >
> > Can someone please direct me to some such examples?
> >
>
>
> How about this:
>
> CREATE TABLE my_table (
> my_table_id BIGSERIAL NOT NULL,
> my_value VARCHAR(100) NOT NULL,
> CONSTRAINT my_table_pkey PRIMARY KEY (my_table_id)
> );
>
> CREATE TABLE my_table_history (
> my_table_id BIGINT NOT NULL,
> my_value VARCHAR(100) NOT NULL,
> create_dt TIMESTAMP NOT NULL,
> CONSTRAINT my_table_history_pkey PRIMARY KEY (my_table_id, create_dt)
> );
>
> 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;
>
> CREATE TRIGGER my_table_history_tr BEFORE UPDATE OR INSERT
> ON my_table FOR EACH ROW
> EXECUTE PROCEDURE my_table_history_fn();
>
> I tried it out and it works in version 8.1.
>
>
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
==================================================================
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Triggers using PL/pgSQL
@ 2006-07-31 12:17 John DeSoi <desoi@pgedit.com>
parent: Aaron Bono <postgresql@aranya.com>
1 sibling, 1 reply; 10+ messages in thread
From: John DeSoi @ 2006-07-31 12:17 UTC (permalink / raw)
To: Aaron Bono <postgresql@aranya.com>; +Cc: Thusitha Kodikara <kthusi@yahoo.com>; pgsql-sql
Is it really necessary to build a SQL string and use execute? It
seems you could just issue the INSERT statement.
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Triggers using PL/pgSQL
@ 2006-07-31 14:59 Aaron Bono <postgresql@aranya.com>
parent: John DeSoi <desoi@pgedit.com>
0 siblings, 2 replies; 10+ messages in thread
From: Aaron Bono @ 2006-07-31 14:59 UTC (permalink / raw)
To: John DeSoi <desoi@pgedit.com>; +Cc: Thusitha Kodikara <kthusi@yahoo.com>; pgsql-sql
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
==================================================================
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Triggers using PL/pgSQL
@ 2006-07-31 15:47 Ken Hill <ken@scottshill.com>
parent: Thusitha Kodikara <kthusi@yahoo.com>
2 siblings, 0 replies; 10+ messages in thread
From: Ken Hill @ 2006-07-31 15:47 UTC (permalink / raw)
To: Thusitha Kodikara <kthusi@yahoo.com>; +Cc: pgsql-sql
On Sun, 2006-07-30 at 21:16 -0700, Thusitha Kodikara wrote:
> Hello,
>
> I am interested in developing some triggers to keep track of records
> that are changed (possibly the changes of one or more specific
> columns). In addition to keeping the new values, I also need to keep
> the old values (may be on a separate table).
>
> Though I have done similar things in other RDBMs using SQL, I find
> doing this in Postgres, a little bit complicated - may be because it
> needs to be done through a separate procedural language and through a
> separate function. The Postgres documentation also didn't provide much
> help ( the examples in C). I was mainly looking for example showing
> how to refer 'OLD' and 'NEW' rows using PL/pgSQL.
>
> Can someone please direct me to some such examples?
>
> Thanks and regards,
> -Thusitha
I too am very interested in this!
-Ken
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Triggers using PL/pgSQL
@ 2006-07-31 15:53 John DeSoi <desoi@pgedit.com>
parent: Aaron Bono <postgresql@aranya.com>
1 sibling, 0 replies; 10+ messages in thread
From: John DeSoi @ 2006-07-31 15:53 UTC (permalink / raw)
To: Aaron Bono <postgresql@aranya.com>; +Cc: Thusitha Kodikara <kthusi@yahoo.com>; pgsql-sql
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
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Triggers using PL/pgSQL
@ 2006-07-31 16:12 Milen A. Radev <milen@radev.net>
parent: Thusitha Kodikara <kthusi@yahoo.com>
2 siblings, 0 replies; 10+ messages in thread
From: Milen A. Radev @ 2006-07-31 16:12 UTC (permalink / raw)
To: pgsql-sql
Thusitha Kodikara написа:
> Hello,
>
> I am interested in developing some triggers to keep track of records that are changed (possibly the changes of one or more specific columns). In addition to keeping the new values, I also need to keep the old values (may be on a separate table).
>
> Though I have done similar things in other RDBMs using SQL, I find doing this in Postgres, a little bit complicated - may be because it needs to be done through a separate procedural language and through a separate function. The Postgres documentation also didn't provide much help ( the examples in C). I was mainly looking for example showing how to refer 'OLD' and 'NEW' rows using PL/pgSQL.
>
> Can someone please direct me to some such examples?
http://www.postgresql.org/docs/8.1/static/plpgsql-trigger.html
--
Milen A. Radev
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Triggers using PL/pgSQL
@ 2006-07-31 16:16 George Weaver <gweaver@shaw.ca>
parent: Aaron Bono <postgresql@aranya.com>
1 sibling, 0 replies; 10+ messages in thread
From: George Weaver @ 2006-07-31 16:16 UTC (permalink / raw)
To: Aaron Bono <postgresql@aranya.com>; John DeSoi <desoi@pgedit.com>; +Cc: Thusitha Kodikara <kthusi@yahoo.com>; pgsql-sql
----- Original Message -----
From: Aaron Bono
To: John DeSoi
<snip>
>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.
You may want to refer to:
http://www.varlena.com/GeneralBits/104.php (Logging Audit Changes with
Composite Typed Columns).
It provides a very concise way of tracking all changes, possible qualifying
as a "best approach"?
Regards,
George
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2006-07-31 16:16 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-07-31 04:16 Triggers using PL/pgSQL Thusitha Kodikara <kthusi@yahoo.com>
2006-07-31 04:52 ` Aaron Bono <postgresql@aranya.com>
2006-07-31 05:30 ` Thusitha Kodikara <kthusi@yahoo.com>
2006-07-31 05:34 ` Aaron Bono <postgresql@aranya.com>
2006-07-31 12:17 ` John DeSoi <desoi@pgedit.com>
2006-07-31 14:59 ` Aaron Bono <postgresql@aranya.com>
2006-07-31 15:53 ` John DeSoi <desoi@pgedit.com>
2006-07-31 16:16 ` George Weaver <gweaver@shaw.ca>
2006-07-31 15:47 ` Ken Hill <ken@scottshill.com>
2006-07-31 16:12 ` Milen A. Radev <milen@radev.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox