Trigger and Notify

From: "Christian Leclerc" <cleclerc(at)ilog(dot)fr>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Trigger and Notify
Date: 2007-05-04 13:51:33
Message-ID: AACB05988D3002498D3BB7C3DDE8A6BF08BE65CC@marbore.ilog.biz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hello,

I'm not sure if this is the right list for my question but as I'm novice
with Postgres, I think it is !
I currently would like to embed a postgres Notify command inside a
trigger function historizing data and notifying subscribers that an
update occurs.
The notification is never received by my C# client application whereas
if I just enter "NOTIFY ODS" in a pgAdmin Query Tool, the notification
is well received by my client.
Does Postgres allow to embed a postgres Notify command inside a trigger
function ? If it is, could you provide me some tips to correctly develop
such a trigger with plperl ?

CREATE OR REPLACE FUNCTION historize_and_notify () RETURNS trigger AS $$
if ($_TD->{event}eq "UPDATE") {
elog(NOTICE, 'Update...');
my $selectQuery = "SELECT column_name FROM
information_schema.columns WHERE table_name = '".$_TD->{relname}."'
ORDER BY ordinal_position";
my $selected = spi_exec_query($selectQuery);
my $status = $selected->{status};
my $nrows = $selected->{processed};
foreach my $rn (0 .. $nrows - 1) {
my $row = $selected->{rows}[$rn];
my $colName = $row->{column_name};
my $newVal = $_TD->{new}{$colName};
my $oldVal = $_TD->{old}{$colName};
if ($newVal ne $oldVal) {
my $historyQuery = "INSERT INTO ODS_HISTORY(table_name,
column_name, row_id, value, change_type, change_date, change_by) VALUES
('".$_TD->{relname}."', '".$colName."', '".$_TD->{new}{id}."',
'".$newVal."', '".$_TD->{event}."', CURRENT_TIMESTAMP, CURRENT_USER)";
my $historized = spi_exec_query($historyQuery);
my $notifyQuery = "NOTIFY ODS";
my $notified = spi_exec_query($notify);
}
}
}
return;
$$ LANGUAGE plperl;

Thanks in advance,
Christian


Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Sean Davis 2007-05-04 13:56:03 Re: Export SELECT statement as SQL queries in file
Previous Message Mario Peshev 2007-05-04 13:45:42 Export SELECT statement as SQL queries in file