Postgres 10 - Targeting schema's function on a multiple schema Database

Question

I'm running PostgresSQL 10, and I have several schemas on my DB with multiple functions. I've created a schemaless script with all the functions on it (I've removed the schema prefix), with this, everytime i create a new schema, I ran the migration and create all the functions as well. This was necessary/requested for a better data separation between customers. All the schemas are twins in terms of structure.

All was fine until I figured that SchemaA was calling a function from public. Even if I call:

SchemaA.myFunction(p_param1:= 'A', p_param2:= 'B').

If this "myFunction" calls another from the inside, it will target public schema by default.

The only way I made it work, was using an input parameter called p_user_schema myFunction(p_param1, p_param2, p_user_schema) and add the following statement as the first line of myFunction body.

EXECUTE FORMAT('SET search_path TO %L', p_user_schema);

I've 147 functions, I will need to adapt each of these, does anyone know a better way to target the callers schema, by callers I mean the prefix schema used on the main call.

Answer 1

You can set the search path at the function level with the current user as the 1st one

CREATE OR REPLACE FUNCTION schemaA.myfunction()
RETURNS ..
AS  $$
 ...
$$ LANGUAGE SQL
SET SEARCH_PATH = "$user", public;