Feature: supporting optional arguments in named/favourite queries?

[tfh-cri]

Hopefully this is the right place, since as best I can tell, the code for \n NAME ARGS... as provided by pgcli actually lives in here.

It would be convenient if there was a way to specify optional arguments to be replaced in a named query, to allow something like:

\ns opt-args select * from some_table where ($?1 is null or some_field like '%$?1%')

so it could be invoked as either \n opt-args with no args to just query everything, or if \n opt-args somevalue to filter to only things matching that argument, without needing to have different queries with/without args (and remembering which you want to use at the time)

I've invented the $?<num> format in the above example which doesn't currently exist. That allows it to be distinguished from normal arguments as they are now, to avoid breaking existing behaviour of the invocation failing if there are unreplaced parameters left.

I'm currently imagining that the optional arg would just be substituted with the value NULL if not present, although a case could be made for a more bash-like ${VAR:-DEFAULT} to allow arbitrary replacement values if not provided.

It might make sense to allow optional aggregated params as well, although not sure if they should expand to an empty list, or also to NULL.

I think I can probably cobble up a PR if there's any interest, but figured it might be wise to check first, and maybe bikeshed the syntax before ploughing ahead 😄

[j-bennet]

Hi @tfh-cri ,

You're correct, the code for the named queries is here:

https://github.com/dbcli/pgspecial/blob/main/pgspecial/namedqueries.py
https://github.com/dbcli/pgspecial/blob/main/pgspecial/iocommands.py

You'd have to change this function to handle your new parameter type:

pgspecial/pgspecial/iocommands.py

Line 179 in f05518e

def subst_favorite_query_args(query, args):

You're welcome to draft a PR. I'd be curious to see some real-life examples of queries for which you want to use this feature.