server side interfaces

Hello,
I am curious to find out how to use a PGSQL interface, from the server, 
within a function.

What I am trying to do is build a replication scheme. I have a set of 
tables, and these tables have a bit field that will be used to indicate the 
replication state. Null for new records, set to 0 for being replicated, then 
1 for replicated.

I want to, from the target server, initiate a:
**********************************************************************
/*Yes I know that PostgreSQL does not like [] but used them to indicate *the 
remote table: ) Sorry if my syntax is off. You will get the idea *though...
*/
BEGIN TRANSACTION REPLICATE;

UPDATE [REMOTE_SERVER].remote_db.remote_table
SET replication = 0 WHERE replication IS NULL;

SELECT INTO local_table
(SELECT * FROM [REMOTE_SERVER].remote_db.remote_table
WHERE replication = 0);

UPDATE [REMOTE_SERVER].remote_db.remote_table
SET replication = 1 WHERE replication = 0;

COMMIT TRANSACTION REPLICATE;
*****************************************************************
In this manner I mark the records, transfer data, then update the original 
table to show that the records in question have been replicated so that they 
will not be replicated twice.

I can do this in a Perl Script, or C, scheduled with cron, but would like to 
build all of the code into the database for obvious reasons (encapsulation, 
speed).

In MSSQL you define a remote db, and the local user that the remote user 
will be impersonating. You do the same for the localdb on the remote. From 
there you can reference a remote table like so:

[hostname].dbname.tablename

BTW: To pre-empt questions and a possible idiot flame about this... I do 
things this way in MSSQL because I am transferring data on a connection that 
we pay for, by the bandwidth we use. MSSQL replication has a terrible amount 
of connection overhead, and is very slow; we cut it down to < 10% by rolling 
our own. It is also much faster now. Maybe we were doing something wrong... 
but it is also much easier to maintain, add and remove servers, and do 
backups this way.

Is there a way to do this in PostGreSQL? Is there an easy way to replicate 
data, from one server to another, that I am overlooking? I can initiate the 
function calls from a cron job, but would prefer to have the actual code in 
the db server so that it will move with the db for dumps, duplication, and 
backups.

I have been researching this for the past week and am still empty handed, 
excepting an external Perl script or possible C or C++ solutions.

thx,
Neil P Davis
_____________________________________________________________________________________
Get more from the Web.  FREE MSN Explorer download : http://explorer.msn.com