Hello,
In fact, trigger and notify works. There was a bug in my code.
The right code is:
my $notifyQuery = "NOTIFY ODS";
my $notified = spi_exec_query($notifyQuery );
instead of:
my $notifyQuery = "NOTIFY ODS";
my $notified = spi_exec_query($notify);
Sorry for the inconvenience,
Christian
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
