Named Queries
Named Queries are a way to save frequently used queries with a short name.
\n
- list all named queries.
\n <name>
- Invoke a named query by its name.
\ns <name> <query>
- Save a new named query called 'name'.
\nd <name>
- Delete an existing named query by its name.
Examples:
# Save a new named query.
> \ns simple select * from abc where a is not Null;
# List all named queries.
> \n
+--------+---------------------------------------+
| Name | Query |
|--------+---------------------------------------|
| simple | SELECT * FROM abc where a is not NULL |
+--------+---------------------------------------+
# Run a named query.
> \n simple
+--------+--------+
| a | b |
|--------+--------|
| 日本語 | 日本語 |
+--------+--------+
# Delete a named query.
> \nd simple
simple: Deleted
Save location
Queries are saved in the [named queries]
section of the
Config file. You can also edit the config
file to manage the named queries.
An example of a query on a single line of the config file:
[named queries]
simple = select * from abc where a is not Null
To create a named query that spans multiple lines do this:
[named queries]
complex = """
select
*
from
abc
where
a is not Null
"""
Positional Parameters
Named queries support shell-style parameter substitution. Save your named
query with parameters as placeholders (e.g. $1
, $2
,
$3
, etc.):
\ns user_by_name select * from users where name = '$1'
When you call a named query with parameters, just add the parameters after the query's name. You can put quotes around arguments that include spaces.
\n user_by_name "Skelly McDermott"
Parameters Aggregation
Named queries also support parameters aggregation via two placeholders.
$*
for raw aggregation and $@
for string aggregation.
The former will use raw values of aggregated parameters, the later will quote
each aggregated value.
Raw Aggregation
\ns users_by_age select * from users where age in ($*)
When you call a named query with parameters, just add any (at least one) parameters after the query's name.
\n users_by_age 42 1337
String Aggregation
\ns users_by_categories select * from users where category in ($@)
When you call a named query with parameters, just add any (at least one) parameters after the query's name. You can put quotes around arguments that include spaces.
\n users_by_categories "home user" "mobile user" superuser
Combining Positional Parameters and Parameters Aggregation
It is possible to combine both positional parameters and parameters aggregation. The positional parameters substitution takes place before the aggregation. Which means positional parameters can be placed after parameters aggregation in the query ; see example bellow. Please note that the positional parameters will not be part of the aggregation taking place afterwards!
\ns users_by_categories_and_age select * from users where name in ($@) and age = $1
\n users_by_categories_and_age 42 "Skelly McDermott" "François Pignon"
The query after substitution would be:
select * from users where name in ('Skelly McDermott', 'François Pignon') and age = 42