Feature proposal for psql

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

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.

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

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 4
db=> \i query1.sql
x | 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.

Perhaps we could add a notion of a "bag of tricks" dir in each user's home
directory, and a slash command \wbag (better name suggestions welcome) that
behaves like \w but assumes the file will go in ~/.psql-bag-of-tricks/ and
\ibag which includes a file from the same dir.