Re: [patch] [doc] Minor variable related cleanup and rewording of plpgsql docs

On Fri, Mar 12, 2021 at 1:36 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> > pá 12. 3. 2021 v 21:08 odesílatel Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> napsal:
> >> I attach a v3 that I like better, although there's room to disagree
> >> about that.
>
> > I am not sure if people can understand the "optimizable command" term.
> More
> > common categories are DML, DDL and SELECT. Maybe it is easier to say. DDL
> > statements don't support parametrizations, and then the variables cannot
> be
> > used there.
>
> Yeah, but DML/DDL is a pretty squishy separation as well, besides
> which it'd mislead people for cases such as CREATE TABLE AS SELECT.
> (Admittedly, I didn't mention that in my version either, but if you
> think in terms of whether the optimizer will be applied then you
> will draw the right conclusion.)
>

Related to an earlier email though, "CREATE TABLE AS SELECT" gets optimized
but "COPY (SELECT) TO" doesn't...

DML/DDL has the merit of being chapters 5 and 6 in the documentation (with
7 being SELECT).

I do agree that the delineation of "returns records or not" is not ideal
here. SELECT, then INSERT/UPDATE/DELETE (due to their shared RETURNING
dynamic), then "DML commands", then "DMS exceptions" (these last two
ideally leveraging the conceptual work noted above). That said, I do not
think this is such a big issue as to warrant that much of a rewrite. But
in lieu of that, and based upon responses given on the mailing lists,
"utility commands" seems preferable to optimizable commands. Defining,
either by name or by behavior, what utility commands are is needed though,
ideally outside of this chapter. Then a paragraph in the "no result"
section should list explicitly those utility commands that are an
exception, since they have an attached SELECT statement that does get
optimized.

Maybe in Chapter 4, with some forward references, some of this can be
covered and the exceptions to the rule (like CREATE TABLE AS) can be
mentioned.

To address your point about "utility commands", lacking an external
definition to link to, I would change it to be "everything except
INSERT/UPDATE/DELETE, which are described below, as well as EXPLAIN and
SELECT which are described in the next section". From there I like my
proposed flow into INSERT/UPDATE/DELETE w/o RETURNING, then from there the
RETURNING pointing forward to these being SELECT-like in behavior.

Adding a note about using EXECUTE works for me.

Calling EXPLAIN a utility command seems incorrect given that it behaves
just like a query. If it quacks like a duck...

What other row set returning commands are you considering as being utility?

> Maybe there's no way out but to specifically list the statement types
> we can insert query parameters in.
>

In the following I'm confused as to why "column reference" is specified
since those are not substituted:

"Parameters will only be substituted in places where a parameter or
column reference is syntactically allowed."

I'm not married to my explicit calling out of identifiers not being
substitutable but that does tend to be what people try to do.

I'm good with the Pl/SQL wording proposal.

David J.