EXECUTE SAVED QUERY

EXECUTE_SAVED_QUERY executes a previously saved query.

Syntax

execute_saved_query_statement ::=
    SELECT EXECUTE_SAVED_QUERY(saved_query_name, [ , argument [ , ... ] ] )

saved_query_name ::= string_literal

argument ::= string_literal | number_literal

Returns

Query execution results, based on the query saved.

Parameters

Parameter	Description
`saved_query_name`	The name of the query to execute
`argument`	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(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):

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

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

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