plpgsql: trigger insert new into other table (archive)

[Matthijs Möhlmann <matthijs@xxxxxxxxxxxx>]

Hello all,

First the explanation:
I have to databases, some_production and some_archive, those two databases
have an identical layout.

Now we need to implement that all insert and update queries should be replicated
to the some_archive database. Well, the question is how to do that?

I thought about the following, implement a trigger function that handles the insert
and update and does the same to the _archive database. Using dblink I am able
to connect to the _archive database. See my trigger function (implemented in
plpgsql):

DECLARE
	qry TEXT;
	conns TEXT[];
BEGIN
	SELECT dblink_get_connections() INTO conns;
	IF (COUNT(conns) = 0) THEN
		SELECT dblink_connect_u('archiveconn', 'dbname=some_archive');
	END IF;

	IF (TG_OP = 'INSERT') THEN
		qry := 'INSERT INTO ' || TG_TABLE_NAME || ' VALUES' || NEW.*;
		SELECT dblink_exec('archiveconn', qry);
	ELSIF (TG_OP = 'UPDATE') THEN
		qry := 'UPDATE ' || TG_TABLE_NAME;
		SELECT dblink_exec('archiveconn', qry);
	END IF;

	RETURN NULL;
END

The query generated in qry has as example the following output:
INSERT INTO test_tbl VALUES(13, somevalue)

And that generates an error of course, somevalue doesn't exist.

If someone has an idea how to solve this I would be greatful!

Maybe there is another approach, in that case let me know.

Regards,

Matthijs Möhlmann

PS: please keep me in the CC as I am not subscribed to this list.



-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general