Re: Desirability of client-side expressions in psql? - Mailing list pgsql-hackers

>>    psql> \if :i >= 5
>>
> I think we're ok with that so long as none of the operators or values has a
> \ in it.
> What barriers do you see to re-using the pgbench grammar?

The pgbench expression grammar mimics SQL expression grammar,
on integers, floats, booleans & NULL.

I'm unsure about some special cases in psql (`shell command`,
'text' "identifier"). They can be forbidden on a new commande (\let),
but what happens on "\if ..." which I am afraid allows them is unclear.

--
Fabien.

(raising this thread from hibernation now that I have the bandwidth)

It seems like the big barriers to just using pgbench syntax are:

  - the ability to indicate that the next thing to follow will be a pgbench expression

  - a way to coax pgbench truth-y values into psql truthy values (t/f, y/n, 1/0)

For that, I see a few ways forward:

1. A suffix on \if, \elif, -exp suffix (or even just -x) to existing commands to indicate that a pgbench expression would follow

This would look something like

    \ifx \elifx \setx

    \if$ \elif$ \set$

2. A command-line-esque switch or other sigil to indicate that what follows is a pgbench expression with psql vars to interpolate

Example:

    \set foo -x 1 + 4

    \set foo \expr 1 + 4

    \if -x :limit > 10

    \if \expr :limit > 10

3. A global toggle to indicate which mode should be used by \if, \elif, and \set

Example:

     \pset expressions [on | off]

4. A combination of #2 and #3 with a corresponding switch/sigil to indicate "do not evaluate pgbench-style

   This is particularly appealing to me because it would allow code snippets from pgbench to be used without modification, while still allowing the user to mix-in old/new style to an existing script.

5. A special variant of `command` where variables are interpolated before being sent to the OS, and allow that on \if, \elif

    \set foo ``expr :y + :z``

    \set foo $( expr :y + :z )

    \if ``expr :limit > 10``
    \if $( expr :limit > 10 )

    This also has some appeal because it allows for a great amount of flexibility, but obviously constrains us with OS-dependencies. The user might have a hard time sending commands with ')' in them if we go the $( ) route

6. Option #5, but we add an additional executable (suggested name: pgexpr) to the client libs, which encapsulates the pgbench expression library as a way around OS-dependent code.

7. I believe someone suggested introducing the :{! pgbench-command} or :{{ pgbench-command }} var-mode 

    \set foo :{! :y + :z }

    \set foo :{{ :y + :z }}

    \if :{! :limit > 10 }

    \if :{{ :limit > 10 }}

    This has some appeal as well, though I prefer the {{...}}  syntax because "!" looks like negation, and {{ resembles the [[ x + y ]] syntax in bash

One nice thing is that most of these options are not mutually exclusive.

Thoughts?