in postgresql, is ti possible to create triggers to a table with triggers on CREATE TABLE?

Question

is is possible in postgres to have a trigger on CREATE TABLE that will create triggers for the newly created table?

for example,

CREATE TABLE base_abc(
   ...
) inherits( base );

I would like to automatically add triggers to the newly create table base_abc that would, for example, that would calculate a column value based on column names.

likewise, is it possible to trigger on ALTER TABLE so that the triggers can be dropped and recreated?

for context, see what is best way to extend postgresql to index json objects?

Do you mind if I ask what kind of thing are you doing that requires dynamic table creation? Not saying it's 100% unbelievable, but let's say that in a very large majority of cases the "right" way is not it. — Amadan, Jun 15 '12 at 08:06
not dynamic table create, but rather dynamic trigger creation on the tables. I have changed the question to clarify. — cc young, Jun 15 '12 at 08:10
By "dynamic table creation", I mean creating tables while your application is running, as opposed to doing it just once when your application is set up. If you're not creating tables after the installation, you wouldn't need a trigger to notify you of such an event. It's kind of like asking "What is the best whetstone I can use to decrease the rate meat falls off from my butter knife before I bring it to my mouth?" without even noticing that maybe, just maybe, a fork would work better for that purpose, rather than a pointier butter knife. Not saying your case is like that, but I *am* curious. — Amadan, Jun 15 '12 at 08:15
Regarding JSON: it is best handled off-database. 1) It is new and experimental in 9.2; 2) PGSQL doesn't actually know anything about JSON except if it's valid or not; and 3) in a majority of cases, you should know the schema of what you're putting in the database ahead of time. — Amadan, Jun 15 '12 at 08:21
agree with everything you have said. for my purposes (and many others I know), the ability to store JSON "objects" and to index on selected attributes would be a quite sufficient bridge for "no-sql" data. if we were to create a "no-sql" type table, having this indexing done automatically makes everything "just work". — cc young, Jun 15 '12 at 08:29
I'll say again: I am not aware PGSQL can handle JSON in any way except for validity. I'd offload the generation of index fields from the DMBS and into your middle tier language; and as for switching JSON schema on runtime, you should have a very very good reason for that. — Amadan, Jun 15 '12 at 08:32
although I appreciate what you say, I do not think we will reach agreement. with an intelligent db, eg, pgsql, I am not at all a fan of putting database maintenance in the middle tier. if you have a dumb repository - why use pg? - then it _has_ to be in the middle tier. but I really don't think there is one correct approach. — cc young, Jun 15 '12 at 08:41

score 2 · Accepted Answer · answered Jun 15 '12 at 08:06

2

Don't think there's a way to implement such "triggers" using PostgreSQL built-in functions, but you can definitely write a stored function that will do what you want - i.e. create a derived table and then a trigger on that table.

You can also write one for ALTERing tables.

answered Jun 15 '12 at 08:06

Sergey Kudriavtsev

10,328
4
43
68

right - no triggers on DDL in postgres, not even CREATE TABLE or ALTER TABLE - seem there is a huge demand, esp from replicators, but not there yet – cc young Jun 20 '12 at 10:26

score 2 · Answer 2 · answered Nov 10 '20 at 15:29

Found this question when googling and since things have changed decided to add contemporary answer. As of 9.3 we have event triggers. Read online docs for more info.

What you ask can be done similar to:

event_tg_example=# CREATE OR REPLACE FUNCTION add_tg_fn()
        RETURNS event_trigger LANGUAGE plpgsql AS $f$
DECLARE
    obj record;
BEGIN
  FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
  LOOP
  execute format('create function %I() returns trigger as $tgf$ begin raise info %L, $i$here$i$; return old; end $tgf$ language $l$plpgsql$l$', 'blah_'||obj.objid::regclass,'%');
  execute format ('CREATE TRIGGER tg_blah BEFORE DELETE ON %I FOR EACH ROW EXECUTE PROCEDURE %I();', obj.objid::regclass, 'blah_'||obj.objid::regclass);

  END LOOP;
END
$f$
;
CREATE FUNCTION
event_tg_example=# CREATE EVENT TRIGGER add_tg_tg
   ON ddl_command_end
WHEN TAG IN ('CREATE TABLE')
   EXECUTE PROCEDURE add_tg_fn()
;
CREATE EVENT TRIGGER
event_tg_example=# create table t(i int);
CREATE TABLE
event_tg_example=# insert into t values (1);
INSERT 0 1
event_tg_example=# delete from t where i = 1;
INFO:  here
DELETE 1
event_tg_example=#

Mind this is just a working example, don't copy/paste it blindly

in postgresql, is ti possible to create triggers to a table with triggers on CREATE TABLE?

2 Answers2