Thread: Moving a simple function to pl/pgsql (Novice question)
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 begindeclare @Found intdeclare @deal varchar(10)select @deal = deal_address from insertedSelect @Found = count(*) from dealbook.dbo.dealswhere deal_address = @dealif @Found > 0 delete from dealbook.dbo.deals where deal_address = @dealinsertinto 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
--- Paul Lambert <paul.lambert@autoledgers.com.au> wrote: > 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? In postgresql, there are two parts to implementing a trigger; the trigger function and the actual trigger on the table that calls this function. Here is a good example: http://www.postgresql.org/docs/8.2/interactive/plpgsql-trigger.html#PLPGSQL-TRIGGER-AUDIT-EXAMPLE One other point to be aware of, Postgresql does not allow your to reference other databases in the DB-cluster directly. It is possible but you need to add a contrib module called dblink: http://pgfoundry.org/projects/snapshot/ Hope this helps, Regards, Richard Broersma Jr.
Richard Broersma Jr wrote: > --- Paul Lambert <paul.lambert@autoledgers.com.au> wrote: >> 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? > > In postgresql, there are two parts to implementing a trigger; the trigger function and the actual > trigger on the table that calls this function. > > Here is a good example: > http://www.postgresql.org/docs/8.2/interactive/plpgsql-trigger.html#PLPGSQL-TRIGGER-AUDIT-EXAMPLE > > One other point to be aware of, Postgresql does not allow your to reference other databases in the > DB-cluster directly. It is possible but you need to add a contrib module called dblink: > http://pgfoundry.org/projects/snapshot/ > > Hope this helps, > Regards, > Richard Broersma Jr. > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > > Perfect, thanks Richard. Regards, Paul. -- Paul Lambert Database Administrator AutoLedgers