pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMoving a simple function to pl/pgsql (Novice question)
3+ messages / 2 participants
[nested] [flat]
* Moving a simple function to pl/pgsql (Novice question)
@ 2007-04-04 06:19 Paul Lambert <paul.lambert@autoledgers.com.au>
2007-04-04 13:06 ` Re: Moving a simple function to pl/pgsql (Novice question) Richard Broersma Jr <rabroersma@yahoo.com>
0 siblings, 1 reply; 3+ messages in thread
From: Paul Lambert @ 2007-04-04 06:19 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Moving a simple function to pl/pgsql (Novice question)
2007-04-04 06:19 Moving a simple function to pl/pgsql (Novice question) Paul Lambert <paul.lambert@autoledgers.com.au>
@ 2007-04-04 13:06 ` Richard Broersma Jr <rabroersma@yahoo.com>
2007-04-04 22:20 ` Re: Moving a simple function to pl/pgsql (Novice question) Paul Lambert <paul.lambert@autoledgers.com.au>
0 siblings, 1 reply; 3+ messages in thread
From: Richard Broersma Jr @ 2007-04-04 13:06 UTC (permalink / raw)
To: Paul Lambert <paul.lambert@autoledgers.com.au>; pgsql-sql
--- 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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Moving a simple function to pl/pgsql (Novice question)
2007-04-04 06:19 Moving a simple function to pl/pgsql (Novice question) Paul Lambert <paul.lambert@autoledgers.com.au>
2007-04-04 13:06 ` Re: Moving a simple function to pl/pgsql (Novice question) Richard Broersma Jr <rabroersma@yahoo.com>
@ 2007-04-04 22:20 ` Paul Lambert <paul.lambert@autoledgers.com.au>
0 siblings, 0 replies; 3+ messages in thread
From: Paul Lambert @ 2007-04-04 22:20 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-04-04 22:20 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-04-04 06:19 Moving a simple function to pl/pgsql (Novice question) Paul Lambert <paul.lambert@autoledgers.com.au>
2007-04-04 13:06 ` Richard Broersma Jr <rabroersma@yahoo.com>
2007-04-04 22:20 ` Paul Lambert <paul.lambert@autoledgers.com.au>
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