PREPARE
Function
PREPARE creates a prepared statement.
A prepared statement is a performance optimizing object on the server. PREPARE is executed to parse, analyze, and rewrite the specified query. EXECUTE is executed to plan and execute the prepared statement. This avoids repetitive parsing and analysis. After the PREPARE statement is created, it exists throughout the database session. Once it is created (even if in a transaction block), it will not be deleted when a transaction is rolled back. It can only be deleted by explicitly invoking DEALLOCATE or automatically deleted when the session ends.
Precautions
Compared with the original openGauss, Dolphin modifies the PREPARE syntax as follows:
The PREPARE FROM syntax is supported.
A statement can be enclosed in single quotation marks, and the statement in the single quotation marks must be a single query. In scenarios where single quotation marks are added, in addition to SELECT, INSERT, UPDATE, DELETE, MERGE INTO, and VALUES statements, other statements that will be converted to SelectStmt are supported, for example, some SHOW statements.
The question mark (
?
) can be used as a binding parameter in a statement. You need to setb_compatibility_mode
toon
first, and$
and?
cannot be used as parameter placeholders in the same statement at the same time. Afterb_compatibility_mode
is set toon
,?
cannot be used as an operator.
Syntax
PREPARE name [ ( data_type [, ...] ) ] { AS | FROM } statement;
PREPARE name [ ( data_type [, ...] ) ] { AS | FROM } 'statement';
Parameter Description
name
Specifies the name of a prepared statement. It must be unique in the session.
data_type
Specifies the data type of the parameter.
statement
Specifies a SELECT, INSERT, UPDATE, DELETE, MERGE INTO, or VALUES statement.
Examples
openGauss=# CREATE TABLE test(name text, age int);
CREATE TABLE
openGauss=# INSERT INTO test values('a',18);
INSERT 0 1
openGauss=# PREPARE stmt FROM SELECT * FROM test;
PREPARE
openGauss=# EXECUTE stmt;
name | age
------+-----
a | 18
(1 row)
openGauss=# set b_compatibility_mode to on;
SET
openGauss=# PREPARE stmt1 FROM 'SELECT sqrt(pow(?,2) + pow(?,2)) as test';;
PREPARE
openGauss=# EXECUTE stmt1 USING 6,8;
test
------
10
(1 row)