In my postgres functions I use the fully qualified name (e.g. schemaname.tablename) for objects within it like tables and other functions.
But in almost all the cases, I'm referring to objects that are in the same schema as the schema of the function itself. How can I let the postgres function know to use by default the same schema as where it was defined for the objects within it?
That way I could have a schema_a.myfunction that refers to mytable but it would resolve to schema_a.mytable, and a schema_b.myfunction that also refers to mytable but it would resolve to schema_b.mytable.
How can I set things up this way? It would really simplify things by making it easy to refactor and rename schemas. I have the same table name across many schemas, so I unfortunately have to use the fully qualified names throughout all functions.
That means if I change something like the schema name I need to rename all occurences of schema_a. to schema_new. in all postgres functions. I'm wondering if there's a better way because sometimes this can be error prone (I could miss a replacement or replace something that I shouldn't have).
You can set the correct search_path when you create the function and use
CREATE FUNCTION ...
SET search_path FROM CURRENT
For that to work, use SET to set search_path to the desired schema before you run the CREATE FUNCTION statement.
Then the search_path will be in effect for the duration of the function.