CODEWITHSUNDEEP
postgresql
Sam Schick
Sam Schick

Reputation: 430

How do I reference tables in the same schema as my function?

I am creating a function within a schema that I am going to make a series of queries from. I want all of these queries to be made as if the schema of the function was first in the search path. I know I can change the search path within the context of the function, but I can't find how to learn the schema that my function is contained in from within my function.

Upvotes: 2

Views: 524

Answers (1)

klin
klin

Reputation: 121784

You can try to parse the pg_context return value of get diagnostics:

create schema test_schema;

create or replace function test_schema.test_function()
returns text language plpgsql as $$
declare
    stack text;
begin
    get diagnostics stack = pg_context;
    return stack;
end $$;

select test_schema.test_function();

                              test_function                              
-------------------------------------------------------------------------
 PL/pgSQL function test_schema.test_function() line 5 at GET DIAGNOSTICS
(1 row)

If the function name is unique you can get the schema name querying the system catalog pg_namespace:

create or replace function test_schema.test_function_2()
returns text language sql as $$
    select nspname::text
    from pg_namespace n
    join pg_proc p on n.oid = pronamespace
    where proname = 'test_function_2'
$$;

select test_schema.test_function_2() as schema_name;

 schema_name 
-------------
 test_schema
(1 row)

Upvotes: 1

Related Questions