Depth of a PostgreSQL function

Question

How can i know the depth of a stored procedure (they are called function in postgresql), that function calls two other recursive function ? Is it possible to know many time that function was called. The reason why i want to do that is because i would like to switch to an iterative approach rather than a recursive which is more efficient in my understanding.

The first function is below:

CREATE OR REPLACE FUNCTION public.generategrid(parameters)
 RETURNS SETOF text AS
$BODY$

DECLARE
      /* input params */

BEGIN
      execute generaterowandcols(parameters)
END;

$BODY$

And as a mentioned above it calls another function

CREATE OR REPLACE FUNCTION public.generaterowandcols(parameters)
$BODY$

DECLARE
      /* input params */

BEGIN
      execute generaterow(parameters)

      execute generaterowandcols(parameters)
END;

$BODY$

And the function that is before calls the fucntion below

CREATE OR REPLACE FUNCTION public.generaterow(parameters)
$BODY$

DECLARE
      /* input params */

BEGIN
      execute generaterow(parameters)

END;

$BODY$

Why do you think you need to know that? What is the underlying problem you are trying to solve with that? — , Mar 26 '18 at 13:26
Legal syntax instead of pseudocode would be much more useful. You seem to have working functions - I assume plpgsql? And you forgot to declare your version of Postgres. — Erwin Brandstetter, Mar 26 '18 at 17:00

score 0 · Answer 1 · answered Mar 26 '18 at 16:10

0

Use GET STACKED DIAGNOSIS to get PG_CONTEXT which contains the call stack.

answered Mar 26 '18 at 16:10

Laurenz Albe

209,280
17
206
263

score -1 · Answer 2 · answered Mar 26 '18 at 13:15

Using the pg_trigger_depth() built in function... ... But this is for trigger function.

CREATE OR REPLACE FUNCTION func() RETURNS void AS $$
BEGIN
    IF pg_trigger_depth() <> 1 THEN
        RAISE NOTICE pg_trigger_depth();
    END IF;
END;
$$ LANGUAGE plpgsql;

Depth of a PostgreSQL function

2 Answers2