Re: « The PL/pgSQL interpreter parses the function's source text and produces an internal binary instruction tree... »

> xof(at)thebuild(dot)com wrote:
>
>> bryn(at)yugabyte(dot)com wrote:
>>
>> It's this that surprises me. And it's this, and only this, that I'm asking about: might _just_ this be a fixable bug?
>
> It might be surprising, but it's not a bug. You can demonstrate it with a very small test case… ["create" succeeds but it]gets an error on execution… Assignment in PL/pgSQL is essentially syntactic sugar around a SELECT ... INTO. Note, however, that this does *not* compile… The reasons, such as they are, are based in how PL/pgSQL processes SQL statements.
>
> "SELECT x INTO y;"
> turns it into "SELECT x ;"
>
> This has the virtue that… but it does result in some of the implementation poking through.

Thanks for those two maximally terse examples, Christophe. They illustrate the same point that my larger examples aimed at. (Forgive me for not working more to distill mine down to what you showed.)

> Unquestionably, this is surprising!

Well, surprise is in the eye of the beholder. I was surprised at first because I hadn't joined the dots from:

« how PL/pgSQL evaluates expressions »

to

«
the moments at which the different flavors of "identifier could not be resolved" error surface:

% is not a known variable

or

column % does not exist
»

But now I've changed the way that I see this—thanks to your replies and to Tom's. See my reply to Tom here:

https://www.postgresql.org/message-id/4DC7ED5B-BADD-4330-B481-76490D3B319E%40yugabyte.com <https://www.postgresql.org/message-id/4DC7ED5B-BADD-4330-B481-76490D3B319E@yugabyte.com>

I'll now adopt a very simple model for when "identifier could not be resolved" errors surface:

« Some surface at "create or replace" time. But many don't surface until runtime. It doesn't help me to look for a reliable specific predictive model here. »

This is what matters:

— The fact that the semantics of (embedded) SQL and expression evaluation are down to a single implementation, and are therefore identical in both top-level SQL and in PL/pgSQL, are enormous. (This stands in stark contrast to Oracle's PL/SQL where there are two implementations that bring inevitable divergences in semantics are limitations.)

— Self-evidently, runtime testing is all that ultimately matters. The more of this I do, and the sooner I do it, the better will be my outcomes.

— The practical advantages of later semantic checking that you've both pointed out are huge. For example, create a temporary table and use it *in the same block statement*.

And now (for the second time) "case closed".