Re: stored procedure namespace bug (critical) + COALECSE notice

[ rather delayed response, sorry about that ]

"Averk" <averk(at)nscan(dot)org> writes:
> When I create temporary table inside a function overriding persistent one,
> usage if it's identifer is bound to persistent table instead of temporary at
> the first function in chain. In any descendant calls or code outside the
> function temporary table is used. See the code.

I don't think this is a namespace issue, but rather a side-effect of the
fact that plpgsql caches query plans upon first use. I'd recommend
using EXECUTE for queries that need to refer to different tables during
different executions.

> Also, COALESCE implementation via CASE suffers much if complex queries
> inside it use some table updates (e.g. additions) - it makes them to insert
> the data TWICE, leading to very unpleasant results. I had to make another
> CASE workaround, but consider making COALESCE more accurate. I guess it's a
> bug, so please check how does this case comply with SQL language itself.

Well, arguably it's not a bug, because the letter of the spec is

2) COALESCE (V1, V2) is equivalent to the following <case specifi-
cation>:

CASE WHEN V1 IS NOT NULL THEN V1 ELSE V2 END

3) COALESCE (V1, V2, . . . ,n ), for n >= 3, is equivalent to the
following <case specification>:

CASE WHEN V1 IS NOT NULL THEN V1 ELSE COALESCE (V2, . . . ,n )
END

However, I don't believe that SQL92 contains any functions with
side-effects, so an implementation that evaluates each argument only
once probably can't be distinguished from the spec by any
spec-conforming test.

We did just commit changes to cause COALESCE to evaluate its arguments
at most once (and NULLIF likewise), because this is clearly the more
desirable behavior in the presence of side-effects, regardless of
whether you consider it 100% compliant to the letter of the spec.

regards, tom lane