Re: Feature proposal for psql

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

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