Polling Connector Module with PostgreSQL

Create Change Table and Triggers

This is going to mirror the MySQL example. So far, I'm assuming the only difference between using MySQL or PostgreSQL for the Polling Connector is the DDL for the tables and trigger.

WARNING

This is only a "work log" for using the Polling Connector with PostgreSQL. As I progress I'll update this into a more usable document.

I've created these objects but haven't yet tested them against Penrose.

CREATE TABLE categories (
	id integer PRIMARY KEY,
	name text,
	description text
);

CREATE SEQUENCE change_number_seq;

CREATE TABLE categories_changes (
	changeNumber integer PRIMARY KEY DEFAULT nextval('change_number_seq'),
	changeTime timestamp with time zone NOT NULL,
	changeAction varchar(10) NOT NULL,
	changeUser varchar(20) NOT NULL,
	id integer NOT NULL
);

-- Used for defining the trigger.
CREATE LANGUAGE plpgsql;

-- I don't think we're supposed to store anything _except_ the
-- primary key in the "changes" table.  This might mean I need to
-- specify OLD.id instead of OLD.* where appropriate.
CREATE OR REPLACE FUNCTION categories_changes() RETURNS TRIGGER AS $cat_change$
    BEGIN
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO categories_changes SELECT 	nextval('change_number_seq'),
							CURRENT_TIMESTAMP(0), 'DELETE',
							user, OLD.id;
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO categories_changes SELECT 	nextval('change_number_seq'),
							CURRENT_TIMESTAMP(0), 'MODIFY',
							user, NEW.id;
            RETURN NEW;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO categories_changes SELECT 	nextval('change_number_seq'),
							CURRENT_TIMESTAMP(0), 'MODIFY',
							user, NEW.id;
            RETURN NEW;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$cat_change$ LANGUAGE plpgsql;

CREATE TRIGGER cat_change
AFTER INSERT OR UPDATE OR DELETE ON categories
    FOR EACH ROW EXECUTE PROCEDURE categories_changes();

References