Re: psql: Add command to use extended query protocol

On Fri, Nov 4, 2022 at 11:45 AM Peter Eisentraut <peter.eisentraut@enterprisedb.com> wrote:

On 02.11.22 01:18, Corey Huinker wrote:
>
>           SELECT $1, $2 \gp 'foo' 'bar'
>
>
> I think this is a great idea, but I foresee people wanting to send that
> output to a file or a pipe like \g allows. If we assume everything after
> the \gp is a param, don't we paint ourselves into a corner?

Any thoughts on how that syntax could be generalized?

A few:

The most compact idea I can think of is to have \bind and \endbind (or more terse equivalents \bp and \ebp)

SELECT * FROM foo WHERE type_id = $1 AND cost > $2 \bind 'param1' 'param2' \endbind $2 \g filename.csv

Maybe the end-bind param isn't needed at all, we just insist that bind params be single quoted strings or numbers, so the next slash command ends the bind list.

If that proves difficult, we might save bind params like registers

something like this, positional:

\bind 1 'param1'
\bind 2 'param2'

SELECT * FROM foo WHERE type_id = $1 AND cost > $2 \g filename.csv
\unbind

or all the binds on one line

\bindmany 'param1' 'param2'

SELECT * FROM foo WHERE type_id = $1 AND cost > $2 \g filename.csv
\unbind

Then psql would merely have to check if it had any bound registers, and if so, the next query executed is extended query protocol, and \unbind wipes out the binds to send us back to regular mode.