Development Versions: 12 / devel
This documentation is for an unsupported version of PostgreSQL.
You may want to view the same page for the current version, or one of the supported versions listed above instead.

DECLARE STATEMENT

DECLARE STATEMENT — declares SQL statement identifier associated with connection

Synopsis

EXEC SQL [ AT connection_name ] DECLARE statement_name STATEMENT

Description

DECLARE STATEMENT declares SQL statement identifier. SQL statement identifier is associated with connection.

DECLARE CURSOR with a SQL statement identifier can be written before PREPARE.

Parameters

connection_name

A database connection name established by the CONNECT command.

If AT clause is omitted, an SQL statement identifier is associated with the DEFAULT connection.

statement_name

The name of a SQL statement identifier, either as an SQL identifier or a host variable.

Notes

AT clause can be used at other dynamic SQL statements. The following table gives the connected database when AT clause is used at DECLARE STATEMENT and other dynamic statements.

Table 35.6. Scenario

Using Scenario Declare Statement Other Dynamic Statements Executed Database
1 Without AT clause Without AT clause Default connection
2 Using AT clause connecting at con1 Without AT clause con1
3 Using AT clause connecting at con1 Using AT clause connecting at con2 con1
4 Without AT clause Using AT clause connecting at con2 con2

In scenario 4, DECLARE STATEMENT will be ignored.

Examples

EXEC SQL CONNECT TO postgres AS con1;
EXEC SQL AT con1 DECLARE sql_stmt STATEMENT;
EXEC SQL DECLARE cursor_name CURSOR FOR sql_stmt;
EXEC SQL PREPARE sql_stmt FROM :dyn_string;
EXEC SQL OPEN cursor_name;
EXEC SQL FETCH cursor_name INTO :column1;
EXEC SQL CLOSE cursor_name;

Compatibility

DECLARE STATEMENT is a PostgreSQL extension of the SQL standard, but can be used in Oracle and DB2.

See Also

CONNECT, DECLARE, OPEN