Re: Variable substitution in psql backtick expansion

2017-04-12 1:42 GMT+02:00 Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>:

>
> Hmmm. Although I do not buy this, it could work as a replacement for \set
>>> which it seems cannot be upgraded because some people may rely on it to
>>> just store whatever comes after it in a variable.
>>>
>>
>> I have no strong opinion on how expressive expressions should be, but
>> having a separate \expr (or \setexpr, etc) gives us a green field to
>> develop them.
>>
>
> Yep.
>
> One possible approach would be to reuse pgbench expression engine in order
> to avoid redevelopping yet another lexer & parser & evaluator. This would
> mean some abstraction work, but it looks like the simplest & most effective
> approach right now. Currently it supports an SQL-expression subset about
> int & float, and there is an ongoing submission to add booleans and a few
> functions. If this is done this way, this suggests that variable management
> should/could be merged as well, but there are some differences (psql
> variables are not typed, it relies on a list, there is a "namespace" thing
> I'm not sure I understood...).
>
> Pavel also suggested some support for TEXT, although I would like to see a
> use case. That could be another extension to the engine.
>

I checked the pgbench expr related code.

Now, there are not a boolean operations, and value compare operations. But
there are lot of functions for random numbers - it is nice for regress
tests.

The text support should be really minimalist - eq op - or can be removed,
if we will have special functions for SQLSTATE (but simple string eq
operation should be useful for writing some tests).

>
> A drawback is that pgbench needs more powerfull client-side expressions
> than psql, thus it adds some useless complexity to psql, but avoiding
> another engine seems desirable.

The pgbench expression language is perfect for us - there is not any new
dependency - it is working on all supported platforms.

Can be nice, if we can reuse pgbench expressions in psql - there are some
task that should be solved first (it is definitely topic for next release)

1. synchronise lexers - the psql lexer doesn't supports types, but
supports variable escaping
2. move pgbench expressions to separate module
3. teach pgbench expressions booleans and strings
4. because pgbench doesn't do early variable evaluation, implementation of
"defined" function is easy - we can introduce some new syntax for
implementation some bash patterns like "default value" or "own undefined
message"
5. we can introduce \setexpr in psql, and \if can use pgbench expr too (the
result of expression) must be boolean value like now
6. the psql builtin variables should be enhanced about server side and
client side numeric versions
7. the psql builtin variables should be enhanced about sqlstate - we are
able to handle errors due setting ON_ERROR_STOP already
8. the psql builtin variables can be enhanced about info about processed
rows

There is a benefit for pgbench - the code can be reduced after migration
expr related code to independent module.

The pgbench can take \if command and \setexpr command (although \setexpr
can be redundant there, there can be nice compatibility with psql)

Regards

Pavel

>
> --
> Fabien.
>