Moving a simple function to pl/pgsql (Novice question)

From: Paul Lambert <paul(dot)lambert(at)autoledgers(dot)com(dot)au>
To: pgsql-sql(at)postgresql(dot)org
Subject: Moving a simple function to pl/pgsql (Novice question)
Date: 2007-04-04 06:19:31
Message-ID: 46134373.40504@autoledgers.com.au
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Roger Tannous 2007-04-04 12:00:56 Generating dates prior to generate_series
Previous Message A. Kretschmer 2007-04-04 05:01:16 Re: plpgsql function question