Prepared Statements

SereneDB supports prepared statements where parameters are substituted when the query is executed. This can improve readability and is useful for preventing SQL injections.

Syntax

There are three syntaxes for denoting parameters in prepared statements: auto-incremented (?), positional ($1), and named ($param). Note that not all clients support all of these syntaxes, e.g., the JDBC client only supports auto-incremented parameters in prepared statements.

Example Dataset

In the following, we introduce the three different syntaxes and illustrate them with examples using the following table.

SQL

CREATE TABLE person (name VARCHAR, age BIGINT);
INSERT INTO person VALUES ('Alice', 37), ('Ana', 35), ('Bob', 41), ('Bea', 25);

In our example query, we'll look for people whose name starts with a B and are at least 40 years old. This will return a single row <'Bob', 41>.

Auto-Incremented Parameters: ?

SereneDB supports using prepared statements with auto-incremented indexing, i.e., the position of the parameters in the query corresponds to their position in the execution statement. For example:

SQL

PREPARE query_person AS    SELECT *    FROM person    WHERE starts_with(name, ?)      AND age >= ?;

Using the CLI client, the statement is executed as follows.

SQL

EXECUTE query_person('B', 40);

 name | age
------+-----
 Bob  |  41

Positional Parameters: $1

Prepared statements can use positional parameters, where parameters are denoted with an integer ($1, $2). For example:

SQL

PREPARE query_person AS    SELECT *    FROM person    WHERE starts_with(name, $2)      AND age >= $1;

Using the CLI client, the statement is executed as follows. Note that the first parameter corresponds to $1, the second to $2, and so on.

SQL

EXECUTE query_person(40, 'B');

 name | age
------+-----
 Bob  |  41

Named Parameters: $parameter

SereneDB also supports named parameters where parameters are denoted with $parameter_name. For example:

SQL

PREPARE query_person AS    SELECT *    FROM person    WHERE starts_with(name, $name_start_letter)      AND age >= $minimum_age;

Using the CLI client, the statement is executed as follows.

SQL

EXECUTE query_person(name_start_letter := 'B', minimum_age := 40);

 name | age
------+-----
 Bob  |  41

Declared Parameter Types

PREPARE name(type, ...) declares the types of the positional parameters $1, $2, and so on. EXECUTE converts each argument to the declared type and fails when an argument cannot be converted. Declared parameters count toward the arguments EXECUTE expects, even when the query does not use them, and EXECUTE with a different number of arguments fails. Parameters without a declared type get their type from the query.

SQL

PREPARE older_than(BIGINT) AS    SELECT name    FROM person    WHERE age > $1;
EXECUTE older_than('40');

 name
------
 Bob

A client can also declare parameter types when it prepares a statement over the PostgreSQL protocol, as most drivers do for their decimal and JSON values. A parameter declared numeric or numeric[] keeps the digits it is sent with: the statement rounds it only where it stores or casts it into a DECIMAL(WIDTH, SCALE), and a comparison sees its exact value. Such a parameter holds up to 38 digits: digits after the decimal point past the 38th are rounded, and a value with more than 38 digits before the decimal point fails with SQLSTATE 22003. NaN and the infinities are refused, as for DECIMAL. A parameter declared jsonb or jsonb[] is read as JSON.

Plans

An execution of a prepared statement skips parsing. A statement that reads only inverted indexes and search tables (storage = 'search') also keeps its plan between executions: it is planned again only by the first execution after an index it reads has changed, for example after a refresh made new rows searchable, and that execution's plan is kept in turn. A statement that reads a regular table is planned on every execution.

The values the index takes over are read at execution, so one plan serves them all: the query vector, a full-text query, the conditions the index answers, LIMIT and OFFSET. A parameter in a condition the index cannot answer, one that is itself an operand of AND or OR, and any parameter of a statement that calls ts_dict_agg or ts_offsets is planned with its value, on every execution.

An EXECUTE whose values do not have the parameters' types plans the statement again for that execution: [1, 1] is an INTEGER[] and 2 an INTEGER. Declare the parameters with those types or cast the values; client drivers that send typed parameters are not affected.

SQL

PREPARE nearest AS    SELECT id    FROM doc_idx    ORDER BY emb <-> $1::FLOAT[2]    LIMIT $2::INTEGER;
EXECUTE nearest([1, 1]::FLOAT[2], 2);

 id
----
  2
  1

When a change to a table a statement reads changes the statement's result columns, for example a column added to the table of a SELECT *, EXECUTE fails with cached plan must not change result type (SQLSTATE 0A000); prepare the statement again. A statement whose parameter types are only known from the arguments of each EXECUTE is not affected.

Lifetime

A prepared statement belongs to the session that prepared it and lives until it is dropped or the session ends. PREPARE and DEALLOCATE take effect immediately: ROLLBACK does not undo them. A name can be prepared only once; preparing it again fails until the statement is dropped:

SQL

PREPARE older_than(BIGINT) AS    SELECT name    FROM person;

db error: ERROR: prepared statement "older_than" already exists

Names follow the rules for identifiers: an unquoted name is folded to lower case and a quoted name keeps its case, so foo, FOO and "foo" name one statement and "Foo" another.

Statements a client prepares through the extended query protocol share these names, exactly as the client sends them: EXECUTE runs them, DEALLOCATE drops them, and PREPARE cannot reuse their names. EXECUTE fails with SQLSTATE 0A000 for such a statement when it is a COPY ... FROM STDIN, a COPY ... TO STDOUT, an empty query, or an ALTER TABLE ... ADD COLUMN whose default is volatile.

Listing Prepared Statements

The pg_catalog.pg_prepared_statements view lists the prepared statements of the session: the name, the text that prepared it, when it was prepared, the parameter and result types, and from_sql, which is true for statements prepared with PREPARE and false for named statements prepared through the extended query protocol. generic_plans and custom_plans are always NULL.

SQL

SELECT name, array_to_string(parameter_types, ', ') AS parameter_types, from_sqlFROM pg_prepared_statementsWHERE name = 'older_than';

 name       | parameter_types | from_sql
------------+-----------------+----------
 older_than | bigint          | t

Dropping Prepared Statements: DEALLOCATE

To drop a prepared statement, use the DEALLOCATE statement:

SQL

DEALLOCATE query_person;

Alternatively, use:

SQL

DEALLOCATE PREPARE query_person;

DEALLOCATE ALL drops every prepared statement of the session, including the named statements prepared through the extended query protocol. DISCARD ALL drops them too.

SQL

DEALLOCATE ALL;