Re: Triggers using PL/pgSQL

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

On 7/30/06, Thusitha Kodikara <kthusi(at)yahoo(dot)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
==================================================================

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(at)aranya(dot)com> wrote: On 7/30/06, Thusitha Kodikara <kthusi(at)yahoo(dot)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
==================================================================

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(at)yahoo(dot)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(at)aranya(dot)com>* wrote:
>
> On 7/30/06, Thusitha Kodikara <kthusi(at)yahoo(dot)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
==================================================================

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

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?

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
==================================================================

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

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

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

----- 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

I'm having this problem inserting data from my form using PL/pgSQL.
Here is the simplified version of my table and function (this example
does not work, also ):

CREATE TABLE theirry.sample (
staff_id serial PRIMARY KEY NOT NULL,
firstname varchar(100),
lastname varchar(150),
username varchar(35),
identifier varchar(40),
address2 varchar(180),
activated boolean,
activated_keys varchar(32)
);

CREATE OR REPLACE FUNCTION insert_staff_b
(insert_firstname varchar)
RETURNS VOID AS
$$
DECLARE
BEGIN
INSERT INTO theirry.sample
(firstname)
VALUES
(insert_firstname);
RETURN;
END;
$$
LANGUAGE plpgsql;

I have a form with a value firstname then call the query in php

select insert_staff_b('$_POST['firstname']::varchar)

Still I get this error:
Warning: pg_query(): Query failed: ERROR: function
insert_staff_b(character varying) does not exist HINT: No function
matches the given name and argument types. You may need to add explicit
type casts.

Suggestions or maybe a place to read up on this problem.

Thanks in advance,
J

On Aug 9, 2006, at 10:36 PM, PostgreSQL Admin wrote:

> select insert_staff_b('$_POST['firstname']::varchar)
>
>
> Still I get this error:
> Warning: pg_query(): Query failed: ERROR: function insert_staff_b
> (character varying) does not exist HINT: No function matches the
> given name and argument types. You may need to add explicit type
> casts.

Your select statement above has unbalanced single quotes. Assuming
this is not really the issue, I would check the search_path and look
at the function in psql or some admin tool to make sure the function
name does not have different capitalization.

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

PostgreSQL Admin <postgres(at)productivitymedia(dot)com> writes:
> CREATE OR REPLACE FUNCTION insert_staff_b
> (insert_firstname varchar)
> RETURNS VOID AS
> ...
> Still I get this error:
> Warning: pg_query(): Query failed: ERROR: function
> insert_staff_b(character varying) does not exist

Sure looks like it oughta work. One possibility is that you created the
function in a schema that isn't part of the application's search path.
Other than that, look for *really* silly errors, like not creating the
function in the same database the application is connected to ...

regards, tom lane

Thanks for the catch. I've tried:

$connection->execute("SELECT
insert_staff_b('$staff[insert_firstname]'::varchar)");
$connection->execute("SELECT
insert_staff_b('".$staff['insert_firstname']."'::varchar)");

None work... I'm scratching my head on this one.

Thanks,
J

Tom Lane wrote:
> PostgreSQL Admin <postgres(at)productivitymedia(dot)com> writes:
>
>> CREATE OR REPLACE FUNCTION insert_staff_b
>> (insert_firstname varchar)
>> RETURNS VOID AS
>> ...
>> Still I get this error:
>> Warning: pg_query(): Query failed: ERROR: function
>> insert_staff_b(character varying) does not exist
>>
>
> Sure looks like it oughta work. One possibility is that you created the
> function in a schema that isn't part of the application's search path.
> Other than that, look for *really* silly errors, like not creating the
> function in the same database the application is connected to ...
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
>
I never thought of that. I look into it.

Thanks,
J

Thanks,

The search path was the problem. Sometimes it's the simple things.

Big thanks,
J

Glad you found the problem.

On Aug 9, 2006, at 11:42 PM, PostgreSQL Admin wrote:

> $connection->execute("SELECT insert_staff_b('$staff
> [insert_firstname]'::varchar)");
> $connection->execute("SELECT insert_staff_b('".$staff
> ['insert_firstname']."'::varchar)");

If you are creating SQL functions you want to call from PHP, you
might be interested in this simple class:

http://pgedit.com/resource/php/pgfuncall

Then instead of all the quoting issue you have above, you could
simply call your SQL function like a normal PHP method call:

$connection->insert_staff_b($staff['insert_firstname']);

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL