Postgres Pro
Downloads
Home   >   mailing lists

Thread: Trigger and Notify

Trigger and Notify

From
"Christian Leclerc"
Date:
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
 
 

Re: Trigger and Notify

From
"Christian Leclerc"
Date:
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

 
From: Christian Leclerc
Sent: Friday, May 04, 2007 3:52 PM
To: 'pgsql-novice@postgresql.org'
Subject: Trigger and Notify

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
 
 

Re: Trigger and Notify

From
Tom Lane
Date:
"Christian Leclerc" <cleclerc@ilog.fr> writes:
> 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.

So there's something wrong with your C# code.  Maybe you forgot to have
it issue a LISTEN command?

            regards, tom lane