0

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.-

Pavel Stehule
  • 42,331
  • 5
  • 91
  • 94
Max
  • 538
  • 1
  • 6
  • 16
  • SP is user defined function?.. – Vao Tsun Nov 22 '17 at 12:59
  • https://stackoverflow.com/questions/2577168/postgresql-table-creation-time – Vao Tsun Nov 22 '17 at 13:02
  • 1
    @VaoTsun - 2577168 is obsolete - now PostgreSQL has event triggers and this issue can be solved well by system functions. – Pavel Stehule Nov 22 '17 at 13:33
  • @PavelStehule yes, completely agree, btw - why nobody adds this fix to the 2577168? I think absence of `last_ddl` in postgres was a holy war before event triggers – Vao Tsun Nov 22 '17 at 13:52
  • @VaoTsun - partially true, it was possible with some C extensions, but there was not a clean simply solution. The philosophy of PostgreSQL: "do nothing if you can't to do correctly". It has advantages disadvantages. – Pavel Stehule Nov 22 '17 at 14:09
  • Thank you all for the answers!!! – Max Nov 23 '17 at 13:35

1 Answers1

3

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;
Pavel Stehule
  • 42,331
  • 5
  • 91
  • 94