PostgreSQL中实现更新默认值（二）

create table basic_update
(
  t_update timestamp
);

CREATE OR REPLACE FUNCTION update_modified_column()
  RETURNS TRIGGER AS $$
BEGIN
  NEW.t_update = now();
  RETURN NEW;
END;
$$
language ‘plpgsql‘;

create function table_create()
  returns event_trigger
language plpgsql
as $$
DECLARE oid           INT;
        table_name    VARCHAR;
        parent_tables VARCHAR [];
        is_update     BOOL;

BEGIN

  SELECT object_identity
      INTO table_name FROM pg_event_trigger_ddl_commands();

  SELECT objid
      INTO oid FROM pg_event_trigger_ddl_commands();


  SELECT get_parent_tables_by_oid(oid)
      INTO parent_tables;

  大专栏  PostgreSQL中实现更新默认值（二） SELECT parent_tables :: TEXT [] @> ‘{basic_update}‘ :: TEXT []
      INTO is_update;


  IF is_update

  THEN
    EXECUTE ‘CREATE TRIGGER tgr_auto_t_update‘
            || ‘ BEFORE UPDATE ON  ‘
            || table_name
            || ‘ FOR EACH ROW EXECUTE PROCEDURE update_modified_column()‘;
  END IF;

END;

CREATE EVENT TRIGGER etgr_table_create
ON ddl_command_end
WHEN TAG IN (‘CREATE TABLE‘)
EXECUTE PROCEDURE table_create();

create table test
(
  id        varchar default uuid_generate_v4() not null
    constraint test_pkey
    primary key,
  text      varchar,
  t_create  timestamp default now()
)
  inherits (basic_update);

insert into test (text)
values (‘a‘);

update test
set text = ‘b‘