Thread: Re: Feature proposal for psql
On Sat, 19 Sep 2020 at 19:20, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Denis Gantsev <gantsevdenis@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.
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
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 laneThe 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.RegardsDenis
Well, if you want to do it right now, you can do this:
db=> select * from foo;
x | y
----+----
1 | 1
2 | 2
3 | 3
4 | 4
5 | 5
6 | 6
7 | 7
8 | 8
9 | 9
10 | 10
(10 rows)
db=> select * from foo where x = :xval \w query1.sql
db=> \set xval 4db=> \i query1.sqlx | y---+---4 | 4(1 row)
Granted, that involves adding files to the filesystem, setting variables rather than passing parameters, remembering what those variables were, and having the discipline to not have overlapping uses for variable names across multiple files.
So the key shortcomings right now seem to be:
* no way to pass in values to an \i or \ir and no way to locally scope them
* one file per query
Setting variables locally in a \ir would need to somehow push and pop existing variable values because those vars are scoped at the session level, and that might confuse the user when they set the var inside the included file expecting the calling session to keep the value.