Re: psql: Add command to use extended query protocol

On Mon, Nov 7, 2022 at 9:02 PM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
wrote:

>
>
> út 8. 11. 2022 v 3:47 odesílatel Corey Huinker <corey(dot)huinker(at)gmail(dot)com>
> napsal:
>
>> On Mon, Nov 7, 2022 at 4:12 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>
>>> Corey Huinker <corey(dot)huinker(at)gmail(dot)com> writes:
>>> > I thought about basically reserving the \$[0-9]+ space as bind
>>> variables,
>>> > but it is possible, though unlikely, that users have been naming their
>>> > variables like that.
>>>
>>> Don't we already reserve that syntax as Params? Not sure whether there
>>> would be any conflicts versus Params, but these are definitely not legal
>>> as SQL identifiers.
>>>
>>> regards, tom lane
>>>
>>
>> I think Pavel was hinting at something like:
>>
>> \set $1 foo
>> \set $2 123
>> UPDATE mytable SET value = $1 WHERE id = $2;
>>
>
> no, I just proposed special syntax for variable usage like bind variable
>
> like
>
> \set var Ahoj
>
> SELECT $var;
>

Why not extend psql conventions for variable specification?

SELECT :$var$;

Thus:
:var => Ahoj
:'var' => 'Ahoj'
:"var" => "Ahoj"
:$var$ => $n (n => <Ahoj>)

The downside is it looks like dollar-quoting but isn't actually causing
<$Ahoj$> to be produced. Instead psql would have to substitute $n at that
location and internally remember that for this query $1 is the contents of
var.

I would keep the \gp meta-command to force extended mode regardless of
whether the query itself requires it.

A pset variable to control the default seems reasonable as well. The
implication would be that if you set that pset variable there is no way to
have individual commands use simple query mode directly.

David J.