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