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:

1. The PREPARE FROM syntax is supported.

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

3. The question mark (?) can be used as a binding parameter in a statement. You need to set b_compatibility_mode to on first, and $ and ? cannot be used as parameter placeholders in the same statement at the same time. After b_compatibility_mode is set to on, ? 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)