How to run the same function on different schemas

Question

I have multiple schemas / user with the same structure but different data. There are some stored functions executed on these data, and so far, they are stored in each schema. I'd like to store these functions together in a new schema, which would make it easier, updating the code, ... as it would be centralized.

I thought, as the search_path is defined to be "$user",public it would reference to the user of the current session / connection, hence those also queries from different schemas would ultimately have the same search_path.

let's say I have a table T1 for the users u1, u2, u3 and a function which uses this table F1.

Originally, F1 would be in copied into the schemas u1, u2, u3 and running select * from F1() would work for each user. However updating the function would become increasingly difficult with the number of users, so I want to have a new schema functions with only one F1 function inside.

Now, running select * from functions.F1() returns an error, that T1 couldn't be found. But the users search_paths contain still the same information. So why does the search_path change based on the function which is executing it, and how can I prevent it from happening?

There was a mail about this on postgres mailing list: http://postgresql.nabble.com/function-doesn-t-see-change-in-search-path-td4971325.html and the final workaround was my original situation. Maybe something change in the meanwhile?

Answer 1

Actually, my thinking was correct. However when I created the new schema, by exporting the old function, pg_dump added SECURITY DEFINER at the definition of each function.

Changing this to SECURITY INVOKER gives the behavior as expected (by me)

from the documentation:

SECURITY INVOKER indicates that the function is to be executed with the privileges of the user that calls it. That is the default. SECURITY DEFINER specifies that the function is to be executed with the privileges of the user that created it.

Answer 2

Add a table parameter to F1. Then add uf1, uf2, and uf3 to u1, u2, and u3. These functions will just call F1 and pass in the correct table.

Answer 3

Have a look at plproxy. This is what Skype used to run queries over multiple database shards via a proxy database with wrapper functions.

You could also write a wrapper function which finds all of the functions in every schema and calls them.