Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group