How to get the schema of the function actually executing ?

About

With postgres, it's possible to get execution information by obtaining the execution stack 1).

Example

Create a function that returns its own schema

In the below function, we:

  • retrieve the stack as a string
  • extract the first function name via the regular expression function (.*)\..*? line

If you want to extract the full qualified name of the function (ie the schema and the name of the function), you can modify the regular expression with:

function (.*?) line

Create the function that extracts its own schema.

CREATE OR REPLACE FUNCTION my_schema.get_curr_schema()
  RETURNS text
  LANGUAGE plpgsql
AS
$$
DECLARE
  textStack text;
BEGIN
  GET DIAGNOSTICS textStack = PG_CONTEXT;
  return substring(textStack from 'function (.*)\..*? line');
END;
$$;

Call it

select my_schema.get_curr_schema;
my_schema

Powered by ComboStrap