"Alexis Beuraud" <alexis@siatel.com> writes:
> EXECUTE (' set search_path to ' || p_schemaName ); ---- setting the search
> path here!
> FOR result in
> select i
> from TableT
> loop
> return next result;
> END LOOP;
The reason that doesn't do what you expect is that the plan for the
SELECT is cached the first time through. You'll need to use FOR IN
EXECUTE to make this work. Rather than explicitly setting search_path
like that, which is likely to have unpleasant consequences all over the
place (hint: the effects persist after your function exits), you might
want to just insert the schema name into the EXECUTE string:
FOR result IN EXECUTE
'select i from ' || quote_ident(p_schemaName) || '.TableT'
LOOP ...
regards, tom lane