In Postgres is there such thing as an internal version number for functions?

user_9819342 0
Viewed 79

In short, I'm looking to see if I can keep track of a function's update history. What I am looking for is something like an OID that increments any time a function is updated (or a way to increment something whenever a function is updated).

I'll call this hypothetical term proc_vid.

So for example, say I create a new function:

CREATE FUNCTION example1 (arg1, arg2) $$ {something something} $$ LANGUAGE plpgsql;

then in theory the proc_vid = 1

Then say I update the function (same function, just patched code between the $$'s).

CREATE OR UPDATE FUNCTION example1 (arg1, arg2) $$ {something something *something new*} $$ LANGUAGE plpgsql;

then I'd want to be able to see that proc_vid = 2

Alternatively, I could also live with being able to tell THAT the function has been updated (from sql command line / query - not logs), even if I don't have the proc_vid.

postgresql
2 Answers

Incrementing the function version by an event trigger will not help you keeping track of changes because the function number might not be the same in different environments, even though the code is. You could:

  1. compute a hash of the function source code
SELECT md5(prosrc) FROM pg_catalog.pg_proc WHERE oid = 'funcname'::regproc;
  1. use a schema management tool (such as flyway) that keeps track of your current schema version.
user_55241000

PostgreSQL does not keep track of such information. Perhaps you can use an event trigger to log something to a database table whenever CREATE FUNCTION runs.

user_64643080
Related