Thread: Feature proposal for psql
Hello
I have a working proposal for a small feature, which I would describe in one sentence as
"named parametrized queries".
Basically it allows to save something like this in a file:
--psql:MyQuery1
SELECT 42 FROM @0
WHERE true
--psql:end
--psql:MyQuery2
/* updates stuff... */
UPDATE stuff SET ..--psql:end
Then, from inside psql, I can invoke the query by its name, and pass an (or many) argument(s).
For example, invoking it like so:
db=# \nq MyQuery1 blabla
Would substitute literally the "@0" with "blabla" and send it to the backend.
I am aware that saving queries is already possible with variables (:var), and saved variables can be sourced from .psqlrc file.
However, I think passing arguments in bash style is kinda nicer, because I don't need to set any variables beforehand. Also this would allow to have a clean and valid SQL (well kinda, if no placeholders "@" is used) and comments.
I have also added autocompletion.
Should I send the diff to the mailing list, or maybe it's not something useful?
Regards
Denis
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.
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