Re: temp table same name real table

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