Thread

1. Rule won't let me NOTIFY, no matter how hard I try

   Jeff Boes <jboes@nexcerpt.com> — 2004-01-13T21:07:45Z

   Here's the setup: I wanted to write a rule that would fire on an update 
   to one table, and do an update to another table, followed by a notify. 
   My first attempt wasn't acceptable to PG (7.3.4):
   
   create rule "my_rule" as
   on update to table_A
   where new.col_A != old.col_A
   do
      (update table_B ...;
       notify "my_signal";
      );
   
   ... because you can't have a "notify" statement in a rule that fires on 
   update (only select, update, and delete, I guess).
   
   Second attempt was to "hide" the notify in a function:
   
   create function fn_notify(TEXT) returns VOID as '
     execute ''notify " || $1 || "'';
   ' language 'plpgsql';
   
   Oddly enough, this works IF and ONLY IF the rule "my_rule" fires and the 
   internal update statement does not update any rows. If it actually 
   updates a row, then I get this error:
   
   WARNING:  plpgsql: ERROR during compile of fn_notify near line 5
   ERROR:  syntax error at or near ""
   
   What gives? Must I use a trigger to get around this?
   
   
   -- 
   Jeff Boes                                      vox 269.226.9550 ext 24
   Database Engineer                                     fax 269.349.9076
   Nexcerpt, Inc.                                 http://www.nexcerpt.com
               ...Nexcerpt... Extend your Expertise
   
   
   

2. Re: Rule won't let me NOTIFY, no matter how hard I try

   Luis Carlos Ferreira <lcf@tuxedo.com.ar> — 2004-01-15T18:34:39Z

   El Mar 13 Ene 2004 18:07, Jeff Boes escribió:
   >JB: Here's the setup: I wanted to write a rule that would fire on an update
   >JB: to one table, and do an update to another table, followed by a notify.
   >JB: My first attempt wasn't acceptable to PG (7.3.4):
   >JB:
   >JB: create rule "my_rule" as
   >JB: on update to table_A
   >JB: where new.col_A != old.col_A
   >JB: do
   >JB:    (update table_B ...;
   >JB:     notify "my_signal";
   >JB:    );
   >JB:
   >JB: ... because you can't have a "notify" statement in a rule that fires on
   >JB: update (only select, update, and delete, I guess).
   >JB:
   hi,
   
   you have to write to rules
   
   first one (update):
   
   create rule "my_rule" as
   on update to table_A
    where new.col_A != old.col_A
    do
       update table_B ...;
   
   second one (notify):
   
   create rule "my_rule2" as
   on update to table_A
   notify my_rule;
   
   	
   
   >JB: Second attempt was to "hide" the notify in a function:
   >JB:
   >JB: create function fn_notify(TEXT) returns VOID as '
   >JB:   execute ''notify " || $1 || "'';
   >JB: ' language 'plpgsql';
   
   The correct syntax...
   
   create function fn_notify ( text ) RETURNS void as '
   declare
           v_signal alias for $1;
   begin
       execute '' notify "'' || v_signal || ''"'';
       return;
   end;
     ' language 'plpgsql';
   
   ...and re-write the first rule
   
   create rule "my_rule" as
   on update to table_A
    where new.col_A != old.col_A
    do
       ( update table_B ...;
   	SELECT fn_notify('my_signal'); );
   
   CAVEAT: This rule always returns a tuple:
    fn_notify
   -----------
   
   (1 row)
   
   
   -- 
   
   ---------------------------------------------------------------
   Luis Carlos Ferreira                          lcf@tuxedo.com.ar 
   Centro de Cómputos            Junin 2957 - Santa Fe - Argentina
   Sindicato de Luz y Fuerza              Tel.: (54)(342) 4520-075 
   ---------------------------------------------------------------
   
   Estas loco?   come vaca!!