EXECUTE SAVED QUERY¶

EXECUTE_SAVED_QUERY executes a previously saved query.

Permissions¶

Executing a saved query requires SELECT permissions to access the tables referenced in the query.

Syntax¶

    SELECT EXECUTE_SAVED_QUERY("<saved_query_name>", [ , argument [ , ... ] ] )

argument ::= string_literal | number_literal

Returns¶

Query execution results, based on the query saved.

Parameters¶

Parameter	Parameter Type	Description
`saved_query_name`	Identifier	The name of the query to execute
`argument`	STRING or NUMERIC literal	A comma separated list of argument literal values

Notes¶

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
 );

Here’s a peek at the table contents (Download nba.csv):

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