0

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
1

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);
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.