From: Paul Lambert <paul.lambert@autoledgers.com.au>
To: pgsql-sql@postgresql.org
Subject: Moving a simple function to pl/pgsql (Novice question)
Date: Wed, 04 Apr 2007 14:19:31 +0800
Message-ID: <46134373.40504@autoledgers.com.au> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: paul.lambert@autoledgers.com.au
Subject: Re: Moving a simple function to pl/pgsql (Novice question)
In-Reply-To: <46134373.40504@autoledgers.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox