* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Stephen Frost <sfrost@snowman.net> 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