5

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

Improve this question

2 Answers 2

Reset to default
4

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

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.
I left out some crucial information in my answer: you need to set search_path first.
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?
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.
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! :-(
|
0

One possible approach would be to refactor your library and add explicit new argument to every function IN **_schema** text. If you mostly invoke/call your functions from table triggers, then each trigger has implicit parameter TG_TABLE_SCHEMA which you would pass to functions. One of benefit of this approach - you would avoid having multiple duplicated function definitions in every schema and could only define it once in public or my_project_api schema.

Or, another approach: utilize User defined variables in PostgreSQL and set a global session var from each application run to define which schema to use..

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.