Message-ID: <46134373.40504@autoledgers.com.au>
Date: Wed, 04 Apr 2007 14:19:31 +0800
From: Paul Lambert <paul.lambert@autoledgers.com.au>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Moving a simple function to pl/pgsql (Novice question)
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Forgive me in advance for this terribly novice question...

I have the following function which was written in MS SQL Servers 
trigger/rule language...

CREATE TRIGGER [Sync_Deals] ON [dbo].[Deals]
FOR INSERT, UPDATE
AS
begin
	declare @Found int
	declare @deal varchar(10)
	select @deal = deal_address from inserted
	Select @Found = count(*) from dealbook.dbo.deals where deal_address = @deal
	if @Found > 0
		delete from dealbook.dbo.deals where deal_address = @deal
	insert into dealbook.dbo.deals select * from inserted
end

The purpose being when a row in a table in one database is updated, it 
will copy (or replicate I guess) the record into a different table into 
another database in the same server. (deleting said record first if it 
already exists)

What is the best way to do this within Postgres? I assume a trigger is 
the way to go here as well, but having not written a trigger in PG I'm a 
little stuck as to where to start. My client goes live with their new 
system on Tuesday and this function forms part of some custom additions 
they wrote on their previous SQL server database. I'd thus like to make 
sure I know what I'm doing to finalise the conversion before the weekend.

It seems to me I need to create a function to do the copy, and then 
create a trigger to call the function - but I'm not entirely sure - you 
can probably tell I haven't done anything with triggers, functions or 
rules yet. :-)

(BTW: No I am not trying to get someone to do my work for me :-P - I 
have about 30ish triggers of various nature to convert, I just need 
somewhere to start so I can figure out how it's done then I can do the 
rest myself)

TIA,
P.

-- 
Paul Lambert
Database Administrator
AutoLedgers