CODEWITHSUNDEEP
postgresqlplpgsqlpostgresql-9.4event-triggers
Max
Max

Reputation: 538

How to know the date of modification of an SP?

Is it possible to know the date of modification and / or creation of an SP in PostgreSQL 9.4?

I need to identify them to upload them next Deploy.-

Upvotes: 0

Views: 190

Answers (1)

Pavel Stehule
Pavel Stehule

Reputation: 45770

PostgreSQL has not this functionality. You can create own table and update it from event triggers.

create table updates(proc regprocedure primary key, t timestamp);

create or replace function event_trigger_for_ddl_command_end()
returns event_trigger as $$
declare obj record;
begin
  for obj in select * from pg_event_trigger_ddl_commands()
  loop
    if obj.classid = 'pg_proc'::regclass then
      insert into updates values(obj.objid, current_timestamp)
          on conflict (proc) do update set t = current_timestamp
                             where updates.proc = excluded.proc;
    end if;
  end loop;
end;
$$ language plpgsql;

create event trigger trigger_for_ddl_command_end
  on ddl_command_end
  execute procedure event_trigger_for_ddl_command_end();

create or replace function fx(a int) returns int as $$ select 1 $$ language sql;

postgres=# select * from updates ;
+-------------+----------------------------+
|    proc     |             t              |
+-------------+----------------------------+
| fx(integer) | 2017-11-22 14:21:11.367036 |
+-------------+----------------------------+
(1 row)

-- alternative code without INSERT ON CONFLICT
create or replace function event_trigger_for_ddl_command_end()
returns event_trigger as $$
declare obj record;
begin
  for obj in select * from pg_event_trigger_ddl_commands()
  loop
    if obj.classid = 'pg_proc'::regclass then
      begin
        update updates set t = current_timestamp
           where proc = obj.objid;
        if not found then
          begin
            insert into updates values(obj.objid, current_timestamp);
          exception when unique_violation then
            update updates set t = current_timestamp
               where proc = obj.objid;
          end;
        end if;
    end if;
  end loop;
end;
$$ language plpgsql;

Upvotes: 3

Related Questions