On Sat, 19 Sep 2020 at 19:20, Tom Lane <t...@sss.pgh.pa.us> wrote: > Denis Gantsev <gantsevde...@gmail.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
