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.
