* Tom Lane ([EMAIL PROTECTED]) wrote: > Stephen Frost <[EMAIL PROTECTED]> writes: > > It'll break most of the functions that we have in our production > > systems... They're not security definer functions but it's routine for > > us to switch between different schemas to run a function on. > > > What about pushing all the in-function references down to the > > specific objects referenced at plan creation time (err, I thought this > > was done?)? > > Wouldn't that break exactly the cases you're worried about? It would be > an enormous amount of work, too.
No, because what we tend to do is build up a query in a string and then call it using execute. It doesn't matter to the execute'd string if the references in the functions are mapped to oids or not at creation time (since the query being built in the string couldn't possibly be affected). If the search path is forced to something that'll screw up the query being execute'd tho. The calls to build up the query don't use things in the current search path much (they're generally refering to a seperate specific reference schema). Once the command is built it's then run, but it could be run in a number of different schemas (because they all have basically the exact same set of tables) which is based on the search path. This allows us to have one set of functions (I think we're up to around 80 now) which can work against a number of schemas. Indeed, what I tend to do is set up the search path something like: set search_path = user1_tables, user1_results, func_schema; select do_scan(); set search_path = user2_tables, user2_results, func_schema; select do_scan(); etc, etc. The queries are run against each user's tables and the results put into a seperate schema for each user. Thanks, Stephen
Description: Digital signature