How to know the parent schema name from inside a function

Question

From inside a function, I need to know in which schema the function belongs to. Is there any way to know that in Postgresql?

FYI, current_schema() function provides me the set schema of the current session, which is not what I want. For example, I am in schema test1 now and I can call function test2.test_function(). Now, from inside that function current_schema() will give test1. But I need to get test2, which is the function schema.

Laurenz Albe · Answer 1 · 2017-11-30T05:40:45.777

0

As far as I can tell this is not possible in a reliable way.

The function you provide in your answer does not work:

CREATE FUNCTION public.myfunc() RETURNS text
   LANGUAGE sql
   AS 'SELECT laurenz.parent_schema()';

CREATE FUNCTION laurenz.myfunc() RETURNS text
   LANGUAGE sql
   AS 'SELECT laurenz.parent_schema()';

Your function gives the wrong answer:

test=> SELECT public.myfunc();
 myfunc  
---------
 laurenz
(1 row)

edited Nov 30 '17 at 05:40

answered Nov 29 '17 at 05:56

Laurenz Albe

209,280
17
206
263

Thanks, got an workaround after some research : https://stackoverflow.com/a/47546362/5645769 – Tᴀʀᴇǫ Mᴀʜᴍᴏᴏᴅ Nov 29 '17 at 06:27
I saw that, and I don't think it is a solution. You can only get the name of the function, but there is no way to say for sure in which schema it is and what the arguments are. Remember that there can be many functions with the same name (e.g. `generate_series`). – Laurenz Albe Nov 29 '17 at 17:32
I just needed to know the parent schema name from a function, so this solves my issue for now. Yes, there can be multiple schema having the same function name and that's why I was looking for this, so this will tell me, which specific function is being called (from which schema?). But if you also need to know arguments, I think you can also get that too calling`pg_get_function_arguments(func_oid)`, as we have already got the oid for the function. – Tᴀʀᴇǫ Mᴀʜᴍᴏᴏᴅ Nov 30 '17 at 04:21
I have expanded my answer to explain what I mean. – Laurenz Albe Nov 30 '17 at 05:41
Sorry, you understand my OP wrong. Whenever you call laurenz.parent_schema() it should return laurenz (which is that functions parent schema), that's what I want. So that is not the wrong answer, that is the correct answer. – Tᴀʀᴇǫ Mᴀʜᴍᴏᴏᴅ Nov 30 '17 at 14:26
I don't want to argue with you, and if the function makes you happy, go with it. But then you could also use: `CREATE FUNCTION laurenz.parent_schema() RETURNS text LANGUAGE sql AS $$SELECT 'laurenz'$$;` – Laurenz Albe Nov 30 '17 at 19:20

score 0 · Answer 2 · answered Nov 29 '17 at 06:26

After doing some research and connecting several SO answers, I come up with this solution.

CREATE OR REPLACE FUNCTION parent_schema()
    RETURNS text AS  $$
    DECLARE
        stack text; fcesig text;
        function_oid oid;
        schema_name text;
    BEGIN
        GET DIAGNOSTICS stack = PG_CONTEXT;
        fcesig := substring(stack from 'function (.*?) line');
        function_oid := fcesig::regprocedure::oid;

        SELECT routine_schema INTO schema_name FROM information_schema.routines WHERE regexp_replace(specific_name, '^.+?([^_]+)$', '\1')::int = function_oid;
        RETURN schema_name;
    END;
$$ LANGUAGE plpgsql;

The answers which helped me to form this :
https://stackoverflow.com/a/32016935/5645769
https://stackoverflow.com/a/1347639/5645769
https://stackoverflow.com/a/24034604/5645769

How to know the parent schema name from inside a function

2 Answers2