Michael Lewis <mlewis@entrata.com> writes:
> IMO, you are asking for strange behavior when you overload a table name.
> I expect that one of the people who work down deep down in the code will
> say it is a caching thing that saves system table lookups.
Yeah, I think that on the second call, plpgsql caches a query plan that
references the permanent table, and then it sees no reason to reconsider
that plan on the third call. (Change of search_path is one of the
conditions that would prompt throwing away the cached plan, which
fits that part of the report. But creating a temp table that happens
to shadow a real table is not one.)
If you really really need to do this, I'd counsel using EXECUTE to
ensure no caching happens. But I concur with Michael that it's
fundamentally a bad idea.
regards, tom lane
