Re: Feature proposal for psql

On Sat, 19 Sep 2020 at 19:20, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Denis Gantsev <gantsevdenis(at)gmail(dot)com> writes:
> > I have a working proposal for a small feature, which I would describe in
> > one sentence as
> > "named parametrized queries".
>
> I can see the use of being able to insert parameters into a "macro",
> and you're right that the existing variable-interpolation feature
> can't handle that.
>
> > Basically it allows to save something like this in a file:
>
> > --psql:MyQuery1
> > SELECT 42 FROM @0
> > WHERE true
> > --psql:end
>
... however, that syntax seems pretty horrid. It's unlike
> anything else in PG and it risks breaking scripts that work today.

I actually thought that would be a completely different file from .psqlrc:
hence, no risk of breaking existing scripts.
That particular file would for exemple be pointed by "PGNQFILE" (or
whatever) environment variable.

We don't do "comments that aren't really comments". "@0" as a
> parameter notation is a non-starter as well, because "@" is a
> perfectly legal prefix operator. Besides that, most stuff in
> Postgres is numbered from 1 not 0.
>

indeed, I missed the fact that "@" is an already used operator. I started
with "%s" (like psycopg2), but that would obviously collide too

If I were trying to build this, I'd probably look for ways to
> extend psql's existing variable-interpolation feature rather than
> build something entirely separate. It's not too hard to imagine
> writing a saved query like
>
> \set MyQuery1 'SELECT * FROM :param1 WHERE id = :param2'
>
> and then we need some notation for expanding a variable with
> parameters. With one eye on the existing notations :"foo" and
> :'foo', I'm wondering about something like
>
> :(MyQuery1,table_name,id_value)
>
> which is not very pretty, but it's not commandeering any syntax
> that's likely to be in use in current applications.
>
> BTW, the reason I'm suggesting variable notation for the parameter
> references is that the way you'd really want to write the saved
> query is probably more like
>
> \set MyQuery1 'SELECT * FROM :"param1" WHERE id = :''param2'''
>
> so as to have robust quoting behavior.
>
> One limitation of this approach is that \set can't span lines, so
> writing complex queries would be kinda painful. But that would
> be a good limitation to address separately; \set isn't the only
> metacommand where can't-span-lines is a problem sometimes.
> If you seriously want to pursue adding a feature like this,
> probably the -hackers list is a more appropriate discussion
> forum than -novice.
>
> regards, tom lane
>

The ability to save and retrieve multi-line queries would be quite nice
though, often I would like to save a query too large to type.

I think I don't know psql well enough to propose a viable syntax, so I
guess that would be up to experts here...
But I would be pretty happy to implement it.

Regards
Denis