SAVE_QUERY¶
SAVE_QUERY
saves a query execution plan.
Read more in the Saved Queries guide.
See also: EXECUTE_SAVED_QUERY, DROP_SAVED_QUERY, SHOW_SAVED_QUERY, LIST_SAVED_QUERIES.
Permissions¶
No special permissions are needed to save a query.
Syntax¶
save_query_statement ::=
SELECT SAVE_QUERY(saved_query_name, parameterized_query_string)
;
saved_query_name ::= string_literal
parameterized_query_string ::= string_literal
Returns¶
If query is saved correctly, this statement does not return anything.
Parameters¶
Parameter |
Description |
---|---|
|
The name of the query to save. This name will identify the query |
|
The query to save. Can be dollar quoted and contain parameters ( |
Notes¶
Query names are unique across the database and can’t be repeated unless dropped.
The query is compiled upon save, but not executed. It must be a valid query at compile time.
Query parameters can be used as substitutes for literal expressions. Parameters cannot be used to substitute identifiers, column names, table names, or other parts of the query.
Query parameters of a string datatype (like
TEXT
) must be of a fixed length, and can be used in equality checks, but not patterns (e.g. LIKE, RLIKE, etc)Query parameters’ types are inferred at compile time.
Examples¶
Assume a table named nba
, with the following structure:
CREATE TABLE nba
(
Name text(40),
Team text(40),
Number tinyint,
Position text(2),
Age tinyint,
Height text(4),
Weight real,
College text(40),
Salary float
);
Here’s a peek at the table contents (Download nba.csv
):
Name |
Team |
Number |
Position |
Age |
Height |
Weight |
College |
Salary |
---|---|---|---|---|---|---|---|---|
Avery Bradley |
Boston Celtics |
0.0 |
PG |
25.0 |
6-2 |
180.0 |
Texas |
7730337.0 |
Jae Crowder |
Boston Celtics |
99.0 |
SF |
25.0 |
6-6 |
235.0 |
Marquette |
6796117.0 |
John Holland |
Boston Celtics |
30.0 |
SG |
27.0 |
6-5 |
205.0 |
Boston University |
|
R.J. Hunter |
Boston Celtics |
28.0 |
SG |
22.0 |
6-5 |
185.0 |
Georgia State |
1148640.0 |
Jonas Jerebko |
Boston Celtics |
8.0 |
PF |
29.0 |
6-10 |
231.0 |
5000000.0 |
|
Amir Johnson |
Boston Celtics |
90.0 |
PF |
29.0 |
6-9 |
240.0 |
12000000.0 |
|
Jordan Mickey |
Boston Celtics |
55.0 |
PF |
21.0 |
6-8 |
235.0 |
LSU |
1170960.0 |
Kelly Olynyk |
Boston Celtics |
41.0 |
C |
25.0 |
7-0 |
238.0 |
Gonzaga |
2165160.0 |
Terry Rozier |
Boston Celtics |
12.0 |
PG |
22.0 |
6-2 |
190.0 |
Louisville |
1824360.0 |
Saving and executing a simple query¶
t=> SELECT SAVE_QUERY('select_all','SELECT * FROM nba');
executed
t=> SELECT EXECUTE_SAVED_QUERY('select_all');
Name | Team | Number | Position | Age | Height | Weight | College | Salary
-------------------------+------------------------+--------+----------+-----+--------+--------+-----------------------+---------
Avery Bradley | Boston Celtics | 0 | PG | 25 | 6-2 | 180 | Texas | 7730337
Jae Crowder | Boston Celtics | 99 | SF | 25 | 6-6 | 235 | Marquette | 6796117
John Holland | Boston Celtics | 30 | SG | 27 | 6-5 | 205 | Boston University |
R.J. Hunter | Boston Celtics | 28 | SG | 22 | 6-5 | 185 | Georgia State | 1148640
[...]
Saving and executing parametrized query¶
Use parameters to replace them later at execution time.
Tip
Use dollar quoting ($$) to avoid escaping strings.
t=> SELECT SAVE_QUERY('select_by_weight_and_team',$$SELECT * FROM nba WHERE Weight > ? AND Team = ?$$);
executed
t=> SELECT EXECUTE_SAVED_QUERY('select_by_weight_and_team', 240, 'Toronto Raptors');
Name | Team | Number | Position | Age | Height | Weight | College | Salary
------------------+-----------------+--------+----------+-----+--------+--------+-------------+--------
Bismack Biyombo | Toronto Raptors | 8 | C | 23 | 6-9 | 245 | | 2814000
James Johnson | Toronto Raptors | 3 | PF | 29 | 6-9 | 250 | Wake Forest | 2500000
Jason Thompson | Toronto Raptors | 1 | PF | 29 | 6-11 | 250 | Rider | 245177
Jonas Valanciunas | Toronto Raptors | 17 | C | 24 | 7-0 | 255 | | 4660482