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.
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
SELECT SAVE_QUERY('select_all','SELECT * FROM nba');
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 ? in the query to denote parameters to be replaced at execution time.
Tip
Use dollar quoting ($$) to avoid escaping strings.
SELECT SAVE_QUERY('select_by_weight_and_team',$$SELECT * FROM nba WHERE Weight > ? AND Team = ?$$);
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
Using an array to transfer a list of values
You can specify a dynamic list of values for a single ? using arrays. The saved query uses <parameter> = ANY (?::<value type>[]) and the call to this saved query requies an array input.
Tip
Use the array list to mimic a dynamic IN clause
The following example uses a list of text values:
SELECT SAVE_QUERY('select_by_weight_and_team_filtered',$$SELECT * FROM nba WHERE Weight > ? AND Team = ? AND height = ANY(?::text[])$$);
SELECT EXECUTE_SAVED_QUERY('select_by_weight_and_team_filtered', 240, 'Toronto Raptors',array['6-9','6-11']);
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
Another example:
SELECT SAVE_QUERY('select_by_team_and_ages',$$SELECT * FROM nba WHERE Team = ? AND age = ANY(?::tinyint[])$$);
SELECT EXECUTE_SAVED_QUERY('select_by_team_and_ages','Toronto Raptors', array[20,23,24]);
Name | Team | Number | Position | Age | Height | Weight | College | Salary
------------------+-----------------+--------+----------+-----+--------+--------+-------------+--------
Bismack Biyombo | Toronto Raptors | 8 | C | 23 | 6-9 | 245 | | 2814000
Bruno Caboclo | Toronto Raptors | 20 | SF | 20 | 6-9 | 205 | | 1524000
Cory Joseph | Toronto Raptors | 6 | PG | 24 | 6-3 | 190 | Texas | 7000000
Lucas Nogueira | Toronto Raptors | 92 | C | 23 | 7-0 | 220 | | 1842000
Norman Powell | Toronto Raptors | 24 | SG | 23 | 6-4 | 215 | UCLA | 650000
Jonas Valanciunas | Toronto Raptors | 17 | C | 24 | 7-0 | 255 | | 4660482
Delon Wright | Toronto Raptors | 55 | PG | 24 | 6-5 | 190 | Utah | 1509360
Permissions
Saving queries requires no special permissions per se, however, it does require from the user to have permissions to access the tables referenced in the query and other query element permissions. The user who saved the query is granted all permissions on the saved query.