[GENERAL] plpgsql: trigger insert new into other table (archive)

[Matthijs Möhlmann]

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] plpgsql: trigger insert new into other table (archive)

[Shaun Thomas]

On 10/09/2012 09:55 AM, Matthijs Möhlmann wrote:


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?


Whatever you do, please don't roll your own. This is a solved problem.
If you plan on doing this kind of replication, please look into Slony,
Bucardo, or Londiste. They're all trigger-based replication systems that
have been in production use by several companies for years now. You're
much better off using them than cobbling something together yourself.

--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-444-8534
stho...@optionshouse.com

__

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to 
this email


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

Re: [GENERAL] plpgsql and insert

[Ben Trewern]

Depending on what client side library you are using you could use the 
RETURNING clause, see the docs: 
http://www.postgresql.org/docs/8.2/static/sql-insert.html

Regards,

Ben
Jamie Deppeler [EMAIL PROTECTED] wrote in message 
news:[EMAIL PROTECTED]
 Hi,

 Have a quick question is possible to record a primary from a insert 
 stament

 eg

 xprimary :=  insert into schema.table(.,.,.,.) VALUES ();


 ---(end of broadcast)---
 TIP 2: Don't 'kill -9' the postmaster
 



---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] plpgsql and insert

[Jamie Deppeler]

Hi,

Have a quick question is possible to record a primary from a insert stament

eg

xprimary :=  insert into schema.table(.,.,.,.) VALUES ();


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] plpgsql and insert

[Andrej Ricnik-Bay]

Please say that again in English?

On 3/5/07, Jamie Deppeler [EMAIL PROTECTED] wrote:

Hi,

Have a quick question is possible to record a primary from a insert stament

eg

xprimary :=  insert into schema.table(.,.,.,.) VALUES ();


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster




--
Please don't top post, and don't use HTML e-Mail :}  Make your quotes concise.

http://www.american.edu/econ/notes/htmlmail.htm

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings