Thread: Triggers using PL/pgSQL

Hello,<br /><br />I am interested in developing some triggers to keep track of records that are changed (possibly the
changesof one or more specific columns). In addition to keeping the new values, I also need to keep the old values (may
beon a separate table).  <br /><br />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
througha separate function. The Postgres documentation also didn't provide much help ( the examples in C). I was mainly
lookingfor example showing how to refer 'OLD' and 'NEW' rows using PL/pgSQL.<br /><br />Can someone please direct me to
somesuch examples?<br /><br />Thanks and regards,<br />-Thusitha<br /> 

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

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

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

Thusitha Kodikara написа:
> Hello,
> 
> I am interested in developing some triggers to keep track of records that are changed (possibly the changes of one or
morespecific 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
Postgresdocumentation also didn't provide much help ( the examples in C). I was mainly looking for example showing how
torefer '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@productivitymedia.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@productivitymedia.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