Postgresql database NOTIFICATION trigger that only returns if some sql is met

postgresqltrigger

I'm trying to get a postgresql trigger that calls a notify function to work but only if the data meets certain criterion.

create trigger notify_site after insert on sites 
for each row execute procedure notify_site();

So far I've got it triggering OK on every row thats insterted into the table using the following trigger.

Which is great.. but what I need to do now is only have it call the pg_notify if site='x' and not if site is something else..

How do I go about that (trigger newbie here, but a google hasn't helped so far).

For bonus points… how would I insert the tablename into the json object being returned by the select all?

Thanks to @a_horse_with_no_name I've managed to get the trigger working selectively quite happily.

Adding the table name to the json on the other hand, not so much.

I've managed to get the function to work without the table name addition, and have managed to get my attempt to actually run without errors, but it produces no output either.

This is what I've come up with

create or replace function public.notify_site()
returns trigger
language plpgsql
as $function$
declare
  payload jsonb := row_to_json(NEW)::text;
begin
payload := jsonb_build_object('tableName', TG_RELNAME) || payload;
perform pg_notify('ppsite', payload::text);
      RETURN new;
    END;
$function$;

But with this code, nothing ever makes it into the queue.

This code works fine, but without the table name

create or replace function public.notify_site()
returns trigger
language plpgsql
as $function$
begin
perform pg_notify('ppdsite', row_to_json(NEW)::text);
      RETURN new;
    END;
$function$;

Any thoughts.

Further on from this thread… I tried the following, which actually ran, but instead of adding the table name to the return, it removed the first two fields entirely from the package sent to the notification…

create or replace function public.notify_site()
returns trigger
language plpgsql
as $function$
declare
  payload jsonb := row_to_json(NEW)::text;
begin
payload := json_build_object('tableName', TG_RELNAME)::jsonb || payload;
perform pg_notify('ppsite', payload::text);
      RETURN new;
    END;
$function$;

The same thing happened if I tried

payload := payload || json_build_object('tableName', TG_RELNAME)::jsonb

Best Answer

You have two options:

1. Do it in the trigger with an IF condition:

if new.site = 'X' then 
  notify ...  
end if;

2. Only fire the trigger if the column contains the value:

create trigger notify_site 
after insert on sites 
for each row 
when (new.site = 'X') 
execute procedure notify_site();

how would I insert the tablename into the json object being returned by the select all?

As documented in the manual the table name is available in the variable TG_RELNAME, so you could just append that to your JSON:

payload := payload || jsonb_build_object('tableName', TG_RELNAME);

Related Solutions

MySQL using trigger instead of check

You do not need a RETURN 1 because Trigger is a Stored Procedure, not a Stored Function.

If you want to break it on purpose, that's acceptable.

I wrote two posts about how to break a trigger midstream

Trigger in MySQL to prevent insertion (Apr 25, 2011)
check constraint does not work? (Dec 23, 2011)

Try this:

CREATE /*!50017 DEFINER = 'root'@'localhost' */ TRIGGER `test`
BEFORE UPDATE ON `apply` 
FOR EACH ROW
BEGIN
    DECLARE dummy INT;

    IF NEW.cname = "hi" THEN          
        SET NEW.cname = "hello";
    ELSE
        SELECT no_such_column INTO dummy
        FROM information_schema.no_such_table;
    END IF;

END; $$

Postgresql – Trigger function check if all NEW.* fields exist in another table

Your attempt fails for multiple reasons. First of all a row is not an array. This construct is just not possible:

FOR field IN NEW LOOP ...

But there is more.

I am not sure I like the general idea. This kind of trigger would run for at least every statement (don't use a row-level trigger for this!), which is quite a bit of overhead. It is also error-prone to put DDL commands in a trigger. Especially if you are a beginner.

That said, here is a proof of concept:

Trigger function:

CREATE OR REPLACE FUNCTION trigger_hv()
  RETURNS trigger AS
$func$
DECLARE
    _sql text;
BEGIN
   SELECT INTO _sql
         'ALTER TABLE ' || quote_ident(TG_RELNAME || '_hv') || ' ADD COLUMN '
       || string_agg(quote_ident(attname) || ' ' || att_type, ', ADD COLUMN ')
   FROM  (
      SELECT attname, format_type(atttypid, atttypmod) AS att_type
      FROM   pg_attribute
      WHERE  attrelid = TG_TABLE_NAME::regclass
      AND    NOT attisdropped   -- no dropped (dead) columns
      AND    attnum > 0         -- no system columns
      ) a
   LEFT   JOIN (
      SELECT attname
      FROM   pg_attribute
      WHERE  attrelid = (TG_TABLE_NAME || '_hv')::regclass
      AND    NOT attisdropped
      AND    attnum > 0
      ) b USING (attname)  -- ignoring data type!
   WHERE  b.attname IS NULL;

   IF _sql IS NOT NULL THEN
      EXECUTE _sql;
   END IF;

   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

Trigger:

CREATE TRIGGER trg_foo_hv
BEFORE INSERT ON foo
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_hv();

SQL Fiddle.

This is a basic proof of concept. It does not check for matching data types and ignores things like schema search_path, NOT NULL constraints or COLLATION.

Related (with more explanation and links):

Best Answer

Related Solutions

MySQL using trigger instead of check

Postgresql – Trigger function check if all NEW.* fields exist in another table

Related Question