4

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).

user779159
  • 9,034
  • 14
  • 59
  • 89

1 Answers1

3

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • 1
    I can't get this to work. `create function` fails saying that the table doesn't exist if I reference a table using only it's name `table_a` in a function `schema_a.function_a`. I would want it to see `table_a` as `schema_a.table_a`. – user779159 Sep 18 '18 at 22:38
  • I left out some crucial information in my answer: you need to set `search_path` first. – Laurenz Albe Sep 19 '18 at 07:22
  • 1
    Is there any way to avoid having to specify `SET search_path FROM CURRENT` on all individual function definitions and have it set as a default somewhere? – user779159 Sep 19 '18 at 09:40
  • No - it is part of the function definition. But given a list of functions and schemas it should be easy to script `ALTER FUNCTION` calls. – Laurenz Albe Sep 19 '18 at 09:42
  • Is there a way to set things up so that just a (re-)setting of the schema (without explicitly altering the search path) changes the namespace resolution inside the function body? Functionally similar to a `this` or `self` namespace in OOP? The only "solution" I could find is *not* setting the search path in the body and not schema-qualifying within the body and then always invoking the function with the appropriate search path set outside of the function. Very brittle! :-( – cstork Dec 11 '21 at 10:06
  • @cstork That should be simple: never reference the schema in the function body and rely on the `search_path` setting on the function. Then you can simply change that. – Laurenz Albe Dec 12 '21 at 04:27
  • Well, but that's exactly the case I excluded in my question. To restate my question: Is there a way to once define (CREATE) a function and make it work (in the sense given) after renaming its schema? Adjusting its `search_path` explicitly after the schema rename assumes special knowledge about the function in said schema. – cstork Dec 16 '21 at 12:50
  • No, that cannot be done. Function bodies and `search_path` settings both are strings and won't change if you rename the schema. – Laurenz Albe Dec 16 '21 at 13:54