Dynamically assign trigger name postgresql

Question

Hi I have a Registra_cambios () function; which want to assign to all tables in my database, I wonder if you can concatenate the trigger name with the record (table name) my cursor to not have the same trigger name on all tables

create trigger example t_log_ "record" ()

CREATE OR REPLACE FUNCTION ActiveTriggers() returns  void as $$
DECLARE

 r record;
c CURSOR FOR SELECT table_name as tab FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE';


BEGIN

  FOR r IN c LOOP
        create trigger t_log_r before insert or update or delete
        on r.tab
        for each row
        execute procedure Registra_cambios();
  END LOOP;




END;
$$ LANGUAGE plpgsql;

Actually triggers is a tables property so its names should by unique only for the same table, not around the schema/database. — Abelisto, Aug 06 '16 at 06:30
@Abelisto if so I want to create the name dynamically trigger, t_log_ name + nametable — Dev. Joel, Aug 06 '16 at 06:32

Vao Tsun · Accepted Answer · 2016-08-06T06:41:51.467

0

soemthing like?..

do
$$
declare
  r record;
begin
  for r in (SELECT table_name as tab FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE';) loop
    execute 'create trigger t_log_r_'||r.tab||' before insert or update or delete
    on '||r.tab||'
    for each row
    execute procedure Registra_cambios()';
  end loop;
end;
$$
;

edited Aug 06 '16 at 06:41

answered Aug 06 '16 at 06:23

Vao Tsun

47,234
13
100
132

I believe `;` semicolon was a problem - I copypasted your statement - should edit it first – Vao Tsun Aug 06 '16 at 06:32
btw I would recommend you using `limit 2` for beginning. to avoid creating triggers for plenty of tables before you sure you want it – Vao Tsun Aug 06 '16 at 06:33
another blind copy paste - you use "tab" as alias for table_name. changed the code... – Vao Tsun Aug 06 '16 at 06:34

Dynamically assign trigger name postgresql

1 Answers1