6

Is it possible to get the current OID within a function? Like:

CREATE FUNCTION foo()
 RETURNS numeric
 LANGUAGE plpgsql
AS '
  BEGIN
    return THIS_FUNCTIONS_OID;
  END
';

I need this, because I created function foo within different schemas so the functions name is not helpful here.

maroonyw
  • 71
  • 5
  • Not sure off the top of my head, but ... *why*? What problem are you attempting to solve with this? – Craig Ringer Aug 14 '15 at 09:11
  • In the end, I want to get the name of the schema in which the function was created. I need this to manipulate the `search_path` at runtime. – maroonyw Aug 14 '15 at 09:14
  • 1
    I don't think it's possible. See [this answer](http://stackoverflow.com/a/12620168/1104979) from one of the main Postgres contributors. He has since added [support for callstack dumps](http://www.postgresql.org/message-id/CAFj8pRChsM1zshEFi0Sy6_VHQpWQ6gr0o6d3qvt9tGG9ovnNNA@mail.gmail.com), which will give you the function name, but unfortunately it doesn't seem to schema-qualify anything. – Nick Barnes Aug 14 '15 at 09:29
  • Thanks for this information! – maroonyw Aug 14 '15 at 11:26

2 Answers2

4

I guess you are looking smth like

return select oid from pg_proc where proname='$0';

I doubt you can get it as variable. You can get the name from current_query(), but it will be very not reliable... Unless you define function name as first argument each time you call it :), then you can use $1, but it is not much reliable either...

Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
2

I don't know what your are doing, but I am sure you don't do it well :). Usually, these strange requirements are related to a strange design and result in code that's hard to maintain.

But you can get the oid of the current function easily with PostgreSQL 9.4 and higher. (This info is easily accessible in C PL functions, but it is hidden in PLpgSQL.) Much easier if your functions are from other schemas than public:

CREATE OR REPLACE FUNCTION omega.inner_func()
RETURNS oid AS  $$
DECLARE
  stack text; fcesig text;
BEGIN
  GET DIAGNOSTICS stack = PG_CONTEXT;
  fcesig := substring(stack from 'function (.*?) line');
  RETURN fcesig::regprocedure::oid;
END;
$$ LANGUAGE plpgsql;

For functions from the public schema it is a little bit more difficult - there is an inconsistency and without explicitly appending the prefix "public" the cast to regprocedure should not work when public is not in search_path. A generic solution needs a few more lines:

CREATE OR REPLACE FUNCTION omega.inner_func()
RETURNS oid AS  $$
DECLARE
  stack text; fcesig text; retoid oid;
BEGIN
  GET DIAGNOSTICS stack = PG_CONTEXT;
  fcesig := substring(stack from 'function (.*?) line');
  retoid := to_regprocedure(fcesig::cstring);
  IF retoid IS NOT NULL THEN RETURN retoid; END IF;
  RETURN to_regprocedure(('public.' || fcesig)::cstring);
END;
$$ LANGUAGE plpgsql;
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Pavel Stehule
  • 42,331
  • 5
  • 91
  • 94
  • "I am sure you don't do it well" -- hmm... @Pavel I used the code you provided (thank you!) so to capture the name of the procedure I'm running. I provide the procedure name when logging success/failure information and/or passing similar info upstream to the caller. Is there a better way to do that? Or am I doing things poorly as well? – Wellspring Oct 02 '19 at 20:15
  • @Wellspring - It's hard to say - usually is not good to have function with same name in more schemas. It's not typical, and then you need do some very specific code. On second hand this code is not pretty crazy - so it is not too bad. It just not typical. Source some strange problems can come from ports from other databases, where there are little bit different functionality. But that is life. – Pavel Stehule Oct 03 '19 at 03:31