Re: Undefined psql variables

Hello Robert,

My 0.02€ about your interesting questions and points.

> On Sun, Apr 2, 2017 at 3:56 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> So my view of this is that "send the expression to the server" ought
>> to be just one option for \if, not the only way to do it.
>
> I heartily agree. There should be some kind of client-side expression
> language, and one thing it should allow is calling out to the server.

Calling the server is already available:

SELECT <whatever> AS varname \gset

What is missing is some client-side expressions.

As \if is a client-side thing, I now think that it should just rely on
client-side evaluation. Note that it is possible to do better, but
solutions are either ugly (strange prefixes) or too clever and possibly
not extensible (regex filtering), and from a user experience point of view
I finally thing that ugly or clever should be avoided.

> Then people who only want to call out to the server can do that, but
> people who want to do something else have the option. Insisting that
> this facility isn't allowed to do anything other than consult the
> server is (1) inconsistent with what we've already got in v10 and (2)
> boxing ourselves into a corner for no very good reason.
>
> Now, the optimal shape for that client-side expression language is not
> very clear to me. Do we want to invent our own language, or maybe
> consider using something that already exists?

> It's been previously suggested that we should somehow embed Lua, and
> this might not be a bad place to consider doing something like that.

I somewhat disagree: Does building postgres should depend on lua? I think
adding such a mandatory dependency would not be a good idea. If it is not
mandatory, then it would mean that psql could be compiled with or without
lua embedding, thus psql would not be dependable because features may or
may not be available when writing a "psql script".

For me, client embedded language pg-{lua,pl,tcl,i?py,bf...} (chose your
favorite:-) projects could make sense, but it does not have to be done
within the existing psql client, especially with trying to keep upward
compatibility... If started, such a thing should be a distinct project,
possibly hosted within postgres source tree if it works well at some
point.

> That might be a way to add a lot of power without having to invent an
> entirely new programming language one bit at a time.

Does programming as such in psql is such a good idea?

ISTM that cpp-like capabilities (include, if, variables, some expressions)
are somewhat both useful and enough for the limited use cases I have
encountered. Similar languages are offered in other instances, such as
readline inputrc or vim vimrc.

If I have something really complicated, then I really want a programming
language, probably I do not want to learn a new one just for this purpose,
so I switch to something else that I already know which will do some SQL
when necessary.

> If we want to invent our own expression language, what kind of syntax
> should it use?

After about 35 years of programming, I've convinced myself that mixing
languages is most often a bad idea (think HTML/CSS/JS/PHP/SQL all in one
file). Currently psql has SQL, backslash commands, :* client-side
variables, all with good justifications. That is somewhat 3 languages (or
2.5 if counting variable substitions for half a language), and I think it
should not go up if avoidable.

This leads to the opinion that if there is a client side language (or
client-side expressions as we are considering here), then it should look
like SQL, hence my constant ranting about the "defined varname" somehow
perlish thing. Tom helped forge this opinion when argumenting about some
pgbench changes I submitted, and he is the one suggesting this.

> Upon what kind of design principles should it be based?

I submit that client side expressions should be a subset of SQL and
possible existing or extended variable substitution.

> There's likely to be vigorous debate on these topics, and probably also
> complaints that the good designs are too much work and the
> easy-to-implement designs are too limiting. (Regular readers of this
> mailing list will likely be able to guess which side of those debates
> I'll be on, but we need to have them all the same.)

I suggest to reuse pgbench expression engine, developed by Haas Robert:-)

I have submitted a patch to add some functions and boolean support, which
seems like a definite requirement for "\if". Although pgbench expressions
are a bit overkill for psql, I think that developing another expression
engine is a bad idea, just reuse the one.

> Regarding the ostensible topic of this thread, one thought I had while
> reading through these various responses is that the original need
> would be well-served by the (somewhat dubious) syntax that bash uses
> for variable substitution.

> Obviously, we aren't going to change the interpolate-this-variable
> character from : to $, but bash has ${parameter:-word} to substitute a
> default for an unset parameter, ${parameter:=word} to substitute a
> default for an unset parameter and also set the parameter to that value,
> ${parameter:?word} to error out with word as the error mesage if
> parameter is not set, and so forth. If we decide to roll our own, we
> might consider taking inspiration from those constructs.

That is somehow what I'm trying to do with the ":?varname" proposal, which
would return TRUE or FALSE depending on whether the variable is defined.

Note that there are a number of issue with bash detailed approach, so the
inspiration needs to be cautious. For instance, bash does not distinguish
easily between undefined and empty, and I think that for a cpp-like
language it should be "easy", as discussed below. The only way to test for
undefined with bash seems to be:

[ "${foo:-bar}" = 'bar' -a "${foo:-foo}" = 'foo' ]

Generating a error message with ${foo:?} is nice, but what psql need is
just a way to test whether a variable is defined or not. Another
difference is that sh substitution escaping are quite different from what
is needed for SQL. The shell escapes when substituting within "", but pg
has two kind of strings with escaping ('' and "") already addressed by the
:'varname' and :"varname" syntaxes. So I think that keeping things
consistent with the current :* syntax is desirable, but it is somehow too
late for redesigning variables, as even if something consistent is thought
of, there would still be the upward compatibility with the :* design which
would make things weird.

> I think that one of the general problems of language design is, as
> Larry Wall once said, that a good language should make simple things
> simple and complex things possible. But simple is not an absolute; it
> depends on context. The things which a language needs to make simple
> are those things which will be done frequently *in that language*. So
> for example in this case, out-calls to SQL need to be very easy to
> write.

If I understand out-calls as server-side, it has been available for years
with gset. I do not like it particularly, but it is there and I do not see
a point to replace it.

> Maybe the empty-parameter thing needs to be easy; not sure.

Empty vs undefined?

> Coming up with a good solution here will involve understanding what
> people typically want to do with a language of this type and then
> making sure that stuff can be done succinctly - and ideally also
> making sure that other stuff is also possible if you're willing to put
> in more legwork.

Basically my current opinion, influenced by Tom, Pavel, Robert and others,
is that:

- server side expression use SELECT & \gset
nothing to do on that front.
- client side expressions are based on an SQL subset
such expressions would appear:
. after \if and \elif
. after \let varname ...
- variable definition is tested with :?varname which is a boolean
constant
- some escaping should be allowed if someone really wants
to write :?varname as such in SQL.

--
Fabien.