Thread: Re: [HACKERS] Variable substitution in psql backtick expansion

On 2 April 2017 at 07:53, Fabien COELHO <coelho@cri.ensmp.fr> wrote:
> Note that this is already available indirectly, as show in the
> documentation.
>
>   SELECT some-boolean-expression AS okay \gset
>   \if :okay
>     \echo boolean expression was true
>   \else
>     \echo boolean expression was false
>   \endif


Am I the only one who thinks that even if \if got the ability to
evaluate arbitrary SQL queries I would probably still always write
things as above? I think putting arbitrary SQL expressions (let alone
queries) would make scripts just a total mess and impossible for
humans to parse.

Whereas storing the results in psql variables and then using those
variables in \if makes even fairly complex queries and nested \if
structures straightforward.  It would also make it far clearer in what
order the queries will be evaluated and under which set of conditions.

I don't think taking a simple command line execution environment like
psql and trying to embed a complete complex language parser in it is
going to result in a sensible programming environment. Having a simple
\if <single variable> is already pushing it. If someone wanted
anything more complex I would strongly recommend switching to perl or
python before trying to code up nesting arbitrary sql in nested
expressions.

-- 
greg

Hello Greg,

>>   SELECT some-boolean-expression AS okay \gset
>>   \if :okay
>
> Am I the only one who thinks that even if \if got the ability to
> evaluate arbitrary SQL queries I would probably still always write
> things as above?

> I think putting arbitrary SQL expressions (let alone queries) would make 
> scripts just a total mess and impossible for humans to parse.

No. Pavel does not like them. Tom wants them to be eventually possible... 
However, fine with me if it is decided that there will never be 
server-side expressions after \if. A good thing is that it potentially 
simplifies minimal \if client-side expressions.

> Whereas storing the results in psql variables and then using those 
> variables in \if makes even fairly complex queries and nested \if 
> structures straightforward. It would also make it far clearer in what 
> order the queries will be evaluated and under which set of conditions.

Hmmm. I'm not sure I get it. The penalty I see is that it adds a 
dummy variable which must be given a sensible name, and for very short 
expressions this is not a win. But this is a minor point.

-- 
Fabien.

Hello Pavel,

> I think so some local expression evaluation could be - but it should not be
> placed in \if statement

Why?

> \expr issupported :VERSION_NUM >= 10000

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.

Maybe \setexpr or \set_expr because it is setting a variable and there is 
already a \set.

> \if :issuported
>
> maybe \if can support the basic logic predicates NOT, OR, AND -

ISTM that "NOT" is a minimal requirement, and the easy one.

Note that OR & AND imply a syntax tree, handling parentheses, not in the 
same league.

> but the operands can be only evaluated variables.

Why?

If your idea was to be followed, it seems to suggest two parsers with 
different constraints, one for the suggested "\expr" and one for the 
existing "\if".

I think that if there is a client expression lexer/parser/executor, there 
would be just one of them for one syntax. Two is one too many.

-- 
Fabien.

> I think so implementation of simple expression evaluation should not be
> hard

Indeed it is not hard, it is rather a matter of deciding what it should 
do, and the syntax to do it.

> - really just - we can expect so any variable will be replaced by
> const in expression
>
> Num (<|>|=|<=|>=) Num

<> and != would seem handy as well.

> Text (<|>|=|<=|>=) Text

What would be the use case for handling TEXT?

> not Bool, Bool (or|and) Bool

Aka logical expressions.

> and special operator "defined"

I'm still not buying this suggestion at all because it does not look like 
SQL and I think that client-side expressions should be a simple subset of 
SQL expressions, which a "defined" operators is definitely not.

>> Hmmm. I'm not sure I get it. The penalty I see is that it adds a dummy
>> variable which must be given a sensible name, and for very short
>> expressions this is not a win. But this is a minor point.

> I know so it is not ideal - but the language with commands "\if", "\else"
> ... is not ideal language.

Sure.

-- 
Fabien.

>> 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.

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.

-- 
Fabien.

    Fabien COELHO wrote:

> Pavel also suggested some support for TEXT, although I would like to see a
> use case. That could be another extension to the engine.

SQLSTATE is text.

Also when issuing "psql -v someoption=value -f script", it's reasonable
to want to compare :someoptionvar to 'somevalue' in the script.


Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

> I checked the pgbench expr related code.

> 2. move pgbench expressions to separate module

Probably already existing "fe_utils".

> 3. teach pgbench expressions booleans

See https://commitfest.postgresql.org/14/985/

-- 
Fabien.

Hello Pavel,

A more detailed answer to your many points.

> 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

Yep. Probably no big deal.

> 2. move pgbench expressions to separate module

Yep, that is needed, "fe_utils" looks like the place as I pointed out 
earlier.

> 3. teach pgbench expressions booleans and strings

Boolean are in progress. For string, ISTM that = <> and maybe 
|| would make sense.

> 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"

Maybe. ISTM that a :* syntax should be thought for so that it always work 
where variable can be used, not only within client side expressions.

Consider:
  \set port 5432

Then you can write:
  SELECT :port ;  -- 5432

And it currently works as expected in SQL. Now I think that the same 
behavior is desirable for variable definition testing, i.e. with a :* 
syntax the substitution can be performed everywhere, eg with:
  \if ...    \set port 5432  \endif

Then it would work both client side:
  \let port_is_defined :?port

and also server side:
  SELECT :?port AS port_is_defined \gset

However I do not think that this can be done cleanly with a "à la perl" 
defined.

> 5. we can introduce \setexpr in psql, and \if can use pgbench expr too (the
> result of expression) must be boolean value like now

Yes.

> 6. the psql builtin variables should be enhanced about server side and
> client side numeric versions

Yes, add some typing where appropriate.

> 7. the psql builtin variables should be enhanced about sqlstate - we are
> able to handle errors due setting ON_ERROR_STOP already

Probably.

> 8. the psql builtin variables can be enhanced about info about processed
> rows

Yep. I've already submitted something about ROW_COUNT and such, see:
    https://commitfest.postgresql.org/14/1103/

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

I now believe that "\let" is the nicest sounding close to set short 
option, and indeed it should be made to work for pgbench as well to keep 
things consistent, for some definition of consistent.

-- 
Fabien.

> I don't think so :?xxx is good idea, because for me :xxx is related to 
> content, not to metadata

Hmmm. Indeed it is not. I do not see it as an issue, but it is a good 
point.

Consider perl "defined $x" or "exists $f{k}". $x/$f{k} should be contents, 
but it is not, the dereferencing is suspended by "defined/exists" Yuk, 
but simple and effective.

Also with CPP: "#define x 1, #ifdef x", somehow "x" should be the value, 
not the name, but yet again it is not dereferenced.

Now consider python: "if 'varname' in locals():" at least it is 
consistent, but I cannot say it looks better in the end:-)

So playing around with a value vs metadata is a frequent trick to keep the 
syntax simple, even if the logic is not all there as you point out.

> and Robert's tip of using bash syntax is more logical for me (to have 
> syntax for default and custom message).

There is no way to simply test for definition in bash, which is exactly 
what is needed...

A second issue with sh-like proposal is that it needs a boundary thing, 
i.e. bash uses braces ${name<operator>value}. If it was the beginning of 
psql I would suggest to consider ${name} stuff, but now I'm not sure that 
such a thing can be introduced like ":{xxx}" ? Maybe that can be done.

However it does not change the issue that sh does not allow to test 
whether a variable is defined, which is the thought for feature. Providing 
a default value or erroring out is not the same thing.

Another question to address: how do you handle ' and " escaping? Pg 
:'name' and :"name" solutions are somewhat horrible, but they are there 
which show that it was needed. I'm not sure how to translate that with 
braces in pg. Maybe :{'name'} and :{"name"}? Hmmm...
Or ":{name}", but then what happens if I write ':{n} x :{m}', should the 
lexer interpolate and escape them inside the strings? That is the sh 
solution, but I'm not sure it should be done now in psql.

> I understand well so it is subjective - and more, don't think so this 
> point is significant.

Well, depending on the syntax things can be done or not, eg test the 
variable definition server-side, not only client side. Hence the 
discussion:-)

> We should to have this functionality.

Yes.

-- 
Fabien.