Thread: Determine the name of the calling function

Postgres 9.2 / pgAdmin III

Linux / Windows XP

Hello,

Within a sub-function, I would like to programmatically determine the name of the calling function.

In a previous programming language, I could call a special built-in function, PROGRAM(n), where n=1 would return the name of the calling program; n=2, the name of the program above that; and so on up the stack.

I've tried out all the special variables associated with "GET STACKED DIAGNOSTICS" statement; but I don't see a way to get the name of the calling program.  And I don’t find this information anywhere else in the PostgreSQL documentation.

How do I programmatically determine, from a sub-function, the name of the calling function?

Thank you, Jack
--
Jack Kaufman
MDS Application Devl (US)
Sanmina-SCI Corporation - Owego site
Email: jack.kaufman@sanmina.com
Skype: jack_kaufman_sanm
607-723-0507

CONFIDENTIALITY
This e-mail message and any attachments thereto, is intended only for use by the addressee(s) named herein and may contain legally privileged and/or confidential information. If you are not the intended recipient of this e-mail message, you are hereby notified that any dissemination, distribution or copying of this e-mail message, and any attachments thereto, is strictly prohibited.  If you have received this e-mail message in error, please immediately notify the sender and permanently delete the original and any copies of this email and any prints thereof.
ABSENT AN EXPRESS STATEMENT TO THE CONTRARY HEREINABOVE, THIS E-MAIL IS NOT INTENDED AS A SUBSTITUTE FOR A WRITING.  Notwithstanding the Uniform Electronic Transactions Act or the applicability of any other law of similar substance and effect, absent an express statement to the contrary hereinabove, this e-mail message its contents, and any attachments hereto are not intended to represent an offer or acceptance to enter into a contract and are not otherwise intended to bind the sender, Sanmina Corporation (or any of its subsidiaries), or any other person or entity.

Jack Kaufman <jack.kaufman@sanmina.com> writes:
> How do I programmatically determine, from a sub-function, the name of the
> calling function?

There's not really any built-in way to do that.

If you are sufficiently desperate, I think it'd work to throw an
exception, catch it, and look into the PG_EXCEPTION_CONTEXT text to pull
out the function name the right number of levels up.  This is likely to
be expensive though.

            regards, tom lane

Tom,

Thank you for your reply.  I'll look into the PG_EXCEPTION_CONTEXT exception text.

Our shop is converting to PostgreSQL & PL/pgSQL and we are figuring out how to apply the language to our environment.  I want to write some API functions that will be called from many other functions.  I want these APIs to log errors to a table and I would like the table entries to include the name of the function that called the API.  Anyway, that's where I'm headed.  Thanks for the support.

Jack

 On Thu, Jan 17, 2013 at 6:43 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

CONFIDENTIALITY
This e-mail message and any attachments thereto, is intended only for use by the addressee(s) named herein and may contain legally privileged and/or confidential information. If you are not the intended recipient of this e-mail message, you are hereby notified that any dissemination, distribution or copying of this e-mail message, and any attachments thereto, is strictly prohibited.  If you have received this e-mail message in error, please immediately notify the sender and permanently delete the original and any copies of this email and any prints thereof.
ABSENT AN EXPRESS STATEMENT TO THE CONTRARY HEREINABOVE, THIS E-MAIL IS NOT INTENDED AS A SUBSTITUTE FOR A WRITING.  Notwithstanding the Uniform Electronic Transactions Act or the applicability of any other law of similar substance and effect, absent an express statement to the contrary hereinabove, this e-mail message its contents, and any attachments hereto are not intended to represent an offer or acceptance to enter into a contract and are not otherwise intended to bind the sender, Sanmina Corporation (or any of its subsidiaries), or any other person or entity.

--As of January 18, 2013 11:28:02 AM -0500, Jack Kaufman is alleged to have
said:

> Our shop is converting to PostgreSQL & PL/pgSQL and we are figuring out
> how to apply the language to our environment.  I want to write some API
> functions that will be called from many other functions.  I want these
> APIs to log errors to a table and I would like the table entries to
> include the name of the function that called the API.  Anyway, that's
> where I'm headed.  Thanks for the support.

--As for the rest, it is mine.

Just a question, as I'm not entirely sure of the answer myself, but would
it be possible in another one of the languages Postgres supports?  This
sounds like a language feature to me, and I know Perl's 'caller' function
would do this for instance, the question is if it would be fully functional
in pgPerl.

Daniel T. Staal

---------------------------------------------------------------
This email copyright the author.  Unless otherwise noted, you
are expressly allowed to retransmit, quote, or otherwise use
the contents for non-commercial purposes.  This copyright will
expire 5 years after the author's death, or in 30 years,
whichever is longer, unless such a period is in excess of
local copyright law.
---------------------------------------------------------------

Jack Kaufman <jack.kaufman@sanmina.com> writes:
> Our shop is converting to PostgreSQL & PL/pgSQL and we are figuring out how
> to apply the language to our environment.  I want to write some API
> functions that will be called from many other functions.  I want these APIs
> to log errors to a table and I would like the table entries to include the
> name of the function that called the API.  Anyway, that's where I'm headed.

Hm ... if you're going to be translating anyway, why not just add the
caller's function name as an explicit parameter?

            regards, tom lane

Tom, a "calling-function-name" parameter to the API may be the best solution I'm going to get with PL/pgSQL.  Thanks for the suggestion.

By the way, PG_EXCEPTION_CONTEXT provided only the (currently executing) API function name and line number of the exception (where I caused a divide-by-zero).  No function-call stack.  :(   But thanks, anyway.

Jack

CONFIDENTIALITY
This e-mail message and any attachments thereto, is intended only for use by the addressee(s) named herein and may contain legally privileged and/or confidential information. If you are not the intended recipient of this e-mail message, you are hereby notified that any dissemination, distribution or copying of this e-mail message, and any attachments thereto, is strictly prohibited.  If you have received this e-mail message in error, please immediately notify the sender and permanently delete the original and any copies of this email and any prints thereof.
ABSENT AN EXPRESS STATEMENT TO THE CONTRARY HEREINABOVE, THIS E-MAIL IS NOT INTENDED AS A SUBSTITUTE FOR A WRITING.  Notwithstanding the Uniform Electronic Transactions Act or the applicability of any other law of similar substance and effect, absent an express statement to the contrary hereinabove, this e-mail message its contents, and any attachments hereto are not intended to represent an offer or acceptance to enter into a contract and are not otherwise intended to bind the sender, Sanmina Corporation (or any of its subsidiaries), or any other person or entity.

Daniel,

Thank you for the "alternative language" idea.  Unfortunately, PL/pgSQL is the only PL on the test database I am learning on.  Of course, other languages may become available to us down the road.  I'll your suggestion in mind.

Thank you, Jack

CONFIDENTIALITY
This e-mail message and any attachments thereto, is intended only for use by the addressee(s) named herein and may contain legally privileged and/or confidential information. If you are not the intended recipient of this e-mail message, you are hereby notified that any dissemination, distribution or copying of this e-mail message, and any attachments thereto, is strictly prohibited.  If you have received this e-mail message in error, please immediately notify the sender and permanently delete the original and any copies of this email and any prints thereof.
ABSENT AN EXPRESS STATEMENT TO THE CONTRARY HEREINABOVE, THIS E-MAIL IS NOT INTENDED AS A SUBSTITUTE FOR A WRITING.  Notwithstanding the Uniform Electronic Transactions Act or the applicability of any other law of similar substance and effect, absent an express statement to the contrary hereinabove, this e-mail message its contents, and any attachments hereto are not intended to represent an offer or acceptance to enter into a contract and are not otherwise intended to bind the sender, Sanmina Corporation (or any of its subsidiaries), or any other person or entity.

Jack Kaufman <jack.kaufman@sanmina.com> writes:
> By the way, PG_EXCEPTION_CONTEXT provided only the (currently executing)
> API function name and line number of the exception (where I caused a
> divide-by-zero).  No function-call stack.  :(   But thanks, anyway.

Really?  I tried this:

create function f1() returns int language plpgsql as $$
declare x int; t text;
begin
  begin
    x := 1/0;
  exception
    when division_by_zero then
      get stacked diagnostics t = pg_exception_context;
      raise notice '%', t;
  end;
  return 0;
end
$$;

create function f2() returns int language plpgsql as $$
begin
  return f1();
end$$;

and I get

regression=# select f2();
NOTICE:  PL/pgSQL function f1() line 5 at assignment
PL/pgSQL function f2() line 3 at RETURN
CONTEXT:  PL/pgSQL function f2() line 3 at RETURN
 f2
----
  0
(1 row)

So the result of pg_exception_context clearly does contain the info you
want.  Pulling it out is left as an exercise for the reader ;-)

Curiously, the behavior doesn't seem to be totally consistent --- when I
run the same case again, I get an additional line of context:

regression=# select f2();
NOTICE:  SQL statement "SELECT 1/0"
PL/pgSQL function f1() line 5 at assignment
PL/pgSQL function f2() line 3 at RETURN
CONTEXT:  PL/pgSQL function f2() line 3 at RETURN
 f2
----
  0
(1 row)

That seems like a bug ...

            regards, tom lane

Hello, Tom,

I found the problem.  I was doing just about the same thing as you, except I was passing the value of "t" up the stack as a return value from the sub- and calling functions.  When I looked in the pgadmin log, I saw what you were seeing.  It looks like "t" has LFs or CRs embedded in the text and I was getting just "line 1", which was the "PL/pgSQL function f1() line 5 at assignment" part.  The part I wanted was in line 2.

The output from PG_EXCEPTION_CONTEXT looks parseable, with line 1 containing the current function, and line 2, the calling function.  The lines are variable length but the LF or CR should be identifiable.  Add in the fact of the variable number of lines you got from PG_EXCEPTION_CONTEXT--where line 1 become 2, and line 2 becomes 3--and the project is a pain but doable.

Thank you for your help.  I don't think I would have figured this out on my own, at least not for quite a while.

Sincerely, Jack

CONFIDENTIALITY
This e-mail message and any attachments thereto, is intended only for use by the addressee(s) named herein and may contain legally privileged and/or confidential information. If you are not the intended recipient of this e-mail message, you are hereby notified that any dissemination, distribution or copying of this e-mail message, and any attachments thereto, is strictly prohibited.  If you have received this e-mail message in error, please immediately notify the sender and permanently delete the original and any copies of this email and any prints thereof.
ABSENT AN EXPRESS STATEMENT TO THE CONTRARY HEREINABOVE, THIS E-MAIL IS NOT INTENDED AS A SUBSTITUTE FOR A WRITING.  Notwithstanding the Uniform Electronic Transactions Act or the applicability of any other law of similar substance and effect, absent an express statement to the contrary hereinabove, this e-mail message its contents, and any attachments hereto are not intended to represent an offer or acceptance to enter into a contract and are not otherwise intended to bind the sender, Sanmina Corporation (or any of its subsidiaries), or any other person or entity.

Jack Kaufman <jack.kaufman@sanmina.com> writes:
> The output from PG_EXCEPTION_CONTEXT looks parseable, with line 1
> containing the current function, and line 2, the calling function.  The
> lines are variable length but the LF or CR should be identifiable.  Add in
> the fact of the variable number of lines you got from
> PG_EXCEPTION_CONTEXT--where line 1 become 2, and line 2 becomes 3--and the
> project is a pain but doable.

Cool.  FYI, I intend to see about fixing the bug --- IMO there should
not be a dependency on the number of executions here, and probably the
form with the extra context line is preferable.

Another thing that you should be aware of before plunging into this is
that GET STACKED DIAGNOSTICS is new as of 9.2; if you're hoping that
this code might be back-portable onto older PG versions, you'd better
not depend on it.

            regards, tom lane

Tom, thank you again for your help in solving the "calling function" problem that I posted last week.  I now have a solution I think our shop can live with, that is, if a better one is not found.  The simplest way to explain the actual solution is to just post the code, which I have done below.  Thank you, again.  Jack
- - - - - - - - - - - - -
--drop function test_get_function_name_top ();
create or replace function test_get_function_name_top () returns text AS $$
begin
  return test_get_function_name ();
end;
$$ language plpgsql;


--drop function test_get_function_name ();
create or replace function test_get_function_name ()
  returns text as $$
declare
  function_name text;
begin
  -- do some stuff
  -- now need name of calling function (

  /* begin - call function name function */
  declare
    temp_var integer;
    exception_context text;
  begin
    temp_var := 1/0;
  exception
    when division_by_zero then
      get stacked diagnostics exception_context = PG_EXCEPTION_CONTEXT;
      function_name := get_function_name (exception_context);
  end;
  /* end - call function name function */

  -- use function name
  -- do more stuff

  return function_name;

end;
$$ language plpgsql;


--drop function get_function_name (exception_context text);
create or replace function get_function_name (exception_context text)
  returns text as $$
declare
    function_name_loc integer;
begin
  function_name_loc = strpos(exception_context, 'PL/pgSQL function') + 18;
  exception_context = substr(exception_context, function_name_loc);
  function_name_loc = strpos(exception_context, 'PL/pgSQL function') + 18;

  if function_name_loc = 18 then
    return 'unknown';
  end if;
     
  exception_context = substr(exception_context, function_name_loc);
  return substr(exception_context, 1, strpos(exception_context, '(') - 1);

  /* Use the "get_function_name" function to determine the name of the calling function.

  To return the name of the calling function, place the following block of code in the main
  function, such that it will be executed before the name of calling function is required:

  declare
    temp_var integer;
    exception_context text;
  begin
    temp_var := 1/0;
  exception
    when division_by_zero then
      get stacked diagnostics exception_context = PG_EXCEPTION_CONTEXT;
      function_name := get_function_name (function_level, exception_context);
  end;

  Also, define the variable "function_name" as "text" in the main function's "declare" section.
  Note that the function "get_function_name" must be executed within the exception block.

  Following the execution of the exception block, the variable "function_name" will contain
  the name of the calling function.  If the main function was not called by another function,
  then the variable "function_name" will contain the value "unknown".
*/

end;
$$ language plpgsql;

select * from test_get_function_name_top ();

CONFIDENTIALITY
This e-mail message and any attachments thereto, is intended only for use by the addressee(s) named herein and may contain legally privileged and/or confidential information. If you are not the intended recipient of this e-mail message, you are hereby notified that any dissemination, distribution or copying of this e-mail message, and any attachments thereto, is strictly prohibited.  If you have received this e-mail message in error, please immediately notify the sender and permanently delete the original and any copies of this email and any prints thereof.
ABSENT AN EXPRESS STATEMENT TO THE CONTRARY HEREINABOVE, THIS E-MAIL IS NOT INTENDED AS A SUBSTITUTE FOR A WRITING.  Notwithstanding the Uniform Electronic Transactions Act or the applicability of any other law of similar substance and effect, absent an express statement to the contrary hereinabove, this e-mail message its contents, and any attachments hereto are not intended to represent an offer or acceptance to enter into a contract and are not otherwise intended to bind the sender, Sanmina Corporation (or any of its subsidiaries), or any other person or entity.

I wrote:
> Curiously, the behavior doesn't seem to be totally consistent --- when I
> run the same case again, I get an additional line of context:

> regression=# select f2();
> NOTICE:  SQL statement "SELECT 1/0"
> PL/pgSQL function f1() line 5 at assignment
> PL/pgSQL function f2() line 3 at RETURN
> CONTEXT:  PL/pgSQL function f2() line 3 at RETURN
>  f2
> ----
>   0
> (1 row)

> That seems like a bug ...

FYI, a fix for this has been committed and will be in 9.2.3.
The code will now consistently include that first line of context.

            regards, tom lane