The problem now becomes keeping this information up to date.

Thankfully, postgresql supports triggers and functions.

First we need to install the language which allows us to create functions. From the command line:

$ createlang plpgsql database_name

There are a few languages you can use to write the functions in (for example, Perl, PHP, Ruby, C, TCL, Python), we'll come back to those perhaps in later tutorials. See documentation for more information.

The plpgsql language is similar to the oracle PL/SQL language so it makes converting from oracle to postgresql quite easy.


Back inside psql, we can create the function.


CREATE OR REPLACE FUNCTION increment_forum_postcount() RETURNS TRIGGER as '
    BEGIN
        UPDATE myforum_forums SET postcount=postcount+1 WHERE forumid=new.forumid;

        UPDATE myforum_threads SET postcount=postcount+1 WHERE threadid=new.threadid;
        RETURN new;
    END
' language'plpgsql';


Using the 'new.' syntax, we can get the forumid & threadid we're inserting in to when we run an insert query into that table. This particular function will update the forum and thread tables with the new postcount. Nice and easy!

Now we have to get postgresql to execute that function every time we insert, which is where a plpgsql trigger comes in handy:

CREATE TRIGGER insert_forum_post AFTER INSERT ON myforum_posts FOR EACH ROW EXECUTE PROCEDURE increment_forum_postcount();

So, after every row that's inserted into the myforum_posts table, it will execute the function called increment_forum_postcount() - the database will update the other tables for us!