PostgreSQL plpgsql获取当前程序OID [英] PostgreSQL plpgsql get current procedures oid

问题描述

是否可以在函数中获取当前的OID?喜欢:

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
';

我需要这个，因为我在不同的架构中创建了函数foo，所以这里的函数名称没有帮助.

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

推荐答案

我不知道您在做什么，但我确定您做得不好:).通常，这些奇怪的要求与一个奇怪的设计有关，并且导致难以维护的代码.

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.

但是使用PostgreSQL 9.4及更高版本可以轻松获得当前函数的oid. (此信息可以在C PL函数中轻松访问，但是在PLpgSQL中是隐藏的.)如果您的函数是来自public:

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;

对于public模式中的函数，要困难一些-不一致，并且如果public不在regprocedure时将不起作用>.通用解决方案还需要几行:

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;

这篇关于PostgreSQL plpgsql获取当前程序OID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！