SAVE QUERY¶

SAVE QUERY saves a query execution plan.

Syntax¶

SELECT SAVE_QUERY("<saved_query_name>", '<parameterized_query_string>')

Returns¶

If query is saved correctly, this statement does not return anything.

Parameters¶

Parameter	Parameter Type	Description
`saved_query_name`	Identifier	The name of the query to save. This name will identify the query
`parameterized_query_string`	STRING literal	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,
   Team TEXT,
   Number TINYINT,
   Position TEXT,
   Age TINYINT,
   Height TEXT,
   Weight REAL,
   College TEXT,
   Salary FLOAT
 );

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 parameters to replace them later 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

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.