Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to get the name of the altered table in a Postgres event trigger?

in a postgres database, have table base1 that is the base table for view view1.

if a column in base1 is created, dropped or renamed, I would like to recreate the view view1 with a ddl trigger.

create event trigger base1_views
   on ddl_command_end
   when tag in( 'ALTER TABLE' )
   execute procedure base1_views_fn();

create function base1_views_fn() returns void as $$
declare
  buf varchar;
begin
  -- is table being altered = 'base'?
  -- add, drop or renaming a column?
  buf = 'drop view view1';
  execute buf;
  buf = 'create view view1 as select * from base1 where ...';
  execute buf;
end;
$$ language 'plpgsql';

within the function base1_views_fn(), how do we obtain the table name and whether we are changing columns?

like image 549
cc young Avatar asked May 04 '14 08:05

cc young


2 Answers

The variables available to plpgsql in a trigger are defined here:

http://www.postgresql.org/docs/9.3/static/plpgsql-trigger.html#PLPGSQL-EVENT-TRIGGER-EXAMPLE

What I can't tell from the text is how many 'event' variables there are. Certainly there are two:

TG_EVENT
Data type text; a string representing the event the trigger is fired for.

TG_TAG
Data type text; variable that contains the command tag for which the trigger is fired.

You can print these in your function to see if they contain the table information you are looking for. The documentation shows a bunch of other variables that are for regular events. I don't know if those will help, but, perhaps TG_TABLE_NAME is set?

like image 58
Greg Avatar answered Sep 28 '22 02:09

Greg


same issue addressed here: How to get SQL text from Postgres event trigger

bottom line, as of now, wait until more features are added to EVENT TRIGGER in future releases

like image 38
cc young Avatar answered Sep 28 '22 03:09

cc young