Postgresql 中文操作指南

36.4. Using Host Variables #

在 Section 36.3中，你学习了如何从嵌入式 SQL 程序执行 SQL 语句。其中一些语句只使用了固定值，并未提供一种方法来向语句中插入用户提供的值，或让程序处理查询返回的值。在实际应用程序中，此类语句并不真正有用。本节详细解释了如何使用称为 host variables_的简单机制在 C 程序和嵌入式 SQL 语句之间传输数据。在嵌入式 SQL 程序中，我们将 SQL 语句视为 C 程序代码中的 _guests，即 host language。因此，C 程序中的变量称为 host variables。

In Section 36.3 you saw how you can execute SQL statements from an embedded SQL program. Some of those statements only used fixed values and did not provide a way to insert user-supplied values into statements or have the program process the values returned by the query. Those kinds of statements are not really useful in real applications. This section explains in detail how you can pass data between your C program and the embedded SQL statements using a simple mechanism called host variables. In an embedded SQL program we consider the SQL statements to be guests in the C program code which is the host language. Therefore the variables of the C program are called host variables.

PostgreSQL 后端和 ECPG 应用程序之间交换值的其他一种方式是使用 SQL 描述符，在 Section 36.7中进行了描述。

Another way to exchange values between PostgreSQL backends and ECPG applications is the use of SQL descriptors, described in Section 36.7.

36.4.1. Overview #

在嵌入式 SQL 中，在 C 程序和 SQL 语句之间传递数据非常简单。您不必让程序将数据粘贴到语句中，这会产生各种复杂情况，如正确实例值，而只需将 C 变量名写入到 SQL 语句中，并使用冒号作为前缀。例如：

Passing data between the C program and the SQL statements is particularly simple in embedded SQL. Instead of having the program paste the data into the statement, which entails various complications, such as properly quoting the value, you can simply write the name of a C variable into the SQL statement, prefixed by a colon. For example:

EXEC SQL INSERT INTO sometable VALUES (:v1, 'foo', :v2);

此语句引用了两个名为 v1 和 v2 的 C 变量，并同样使用了常规 SQL 字符串文本，这表明您不必局限于使用一种或另一种类型的数据。

This statement refers to two C variables named v1 and v2 and also uses a regular SQL string literal, to illustrate that you are not restricted to use one kind of data or the other.

这种在 SQL 语句中插入 C 变量的方式在 SQL 语句中需要值表达式时可用。

This style of inserting C variables in SQL statements works anywhere a value expression is expected in an SQL statement.

36.4.2. Declare Sections #

要在程序和数据库之间传递数据（例如，作为查询中的参数），或者要将数据从数据库传递回程序，则打算包含此数据的 C 变量需要在专门标记的部分中声明，以便嵌入式 SQL 预处理器意识到它们。

To pass data from the program to the database, for example as parameters in a query, or to pass data from the database back to the program, the C variables that are intended to contain this data need to be declared in specially marked sections, so the embedded SQL preprocessor is made aware of them.

此部分以以下内容开头：

This section starts with:

EXEC SQL BEGIN DECLARE SECTION;

并以以下内容结尾：

and ends with:

EXEC SQL END DECLARE SECTION;

在这些行之间，必须有常规的 C 变量声明，例如：

Between those lines, there must be normal C variable declarations, such as:

int   x = 4;
char  foo[16], bar[16];

如您所见，您可以选择为变量分配初始值。变量的作用域由其声明部分在程序中的位置决定。您还可以使用以下语法声明变量，该语法隐式创建声明部分：

As you can see, you can optionally assign an initial value to the variable. The variable’s scope is determined by the location of its declaring section within the program. You can also declare variables with the following syntax which implicitly creates a declare section:

EXEC SQL int i = 4;

您可以在程序中拥有任意数量的声明部分。

You can have as many declare sections in a program as you like.

声明也会作为常规的 C 变量回显到输出文件，因此无需再次声明。不打算在 SQL 命令中使用的变量可以在这些特殊部分之外正常声明。

The declarations are also echoed to the output file as normal C variables, so there’s no need to declare them again. Variables that are not intended to be used in SQL commands can be declared normally outside these special sections.

结构或联合的定义也必须在 DECLARE 部分内列出。否则，预处理器无法处理这些类型，因为它不知道定义。

The definition of a structure or union also must be listed inside a DECLARE section. Otherwise the preprocessor cannot handle these types since it does not know the definition.

36.4.3. Retrieving Query Results #

现在，您应该能够将程序生成的数据传递到 SQL 命令中。但如何检索查询结果？为此，嵌入式 SQL 提供了通常命令的特殊变体 SELECT 和 FETCH。这些命令有一个特殊的 INTO 子句，它指定要将检索到的值存储在哪些主机变量中。SELECT 用于仅返回单行的查询，而 FETCH 用于使用游标返回多行的查询。

Now you should be able to pass data generated by your program into an SQL command. But how do you retrieve the results of a query? For that purpose, embedded SQL provides special variants of the usual commands SELECT and FETCH. These commands have a special INTO clause that specifies which host variables the retrieved values are to be stored in. SELECT is used for a query that returns only single row, and FETCH is used for a query that returns multiple rows, using a cursor.

这是一个示例：

Here is an example:

/*
 * assume this table:
 * CREATE TABLE test1 (a int, b varchar(50));
 */

EXEC SQL BEGIN DECLARE SECTION;
int v1;
VARCHAR v2;
EXEC SQL END DECLARE SECTION;

 ...

EXEC SQL SELECT a, b INTO :v1, :v2 FROM test;

因此，INTO 子句会出现在 select 列表和 FROM 子句之间。select 列表和 INTO 之后的列表（也称为目标列表）中的元素数量必须相等。

So the INTO clause appears between the select list and the FROM clause. The number of elements in the select list and the list after INTO (also called the target list) must be equal.

以下是一个使用命令 FETCH 的示例：

Here is an example using the command FETCH:

EXEC SQL BEGIN DECLARE SECTION;
int v1;
VARCHAR v2;
EXEC SQL END DECLARE SECTION;

 ...

EXEC SQL DECLARE foo CURSOR FOR SELECT a, b FROM test;

 ...

do
{
    ...
    EXEC SQL FETCH NEXT FROM foo INTO :v1, :v2;
    ...
} while (...);

此处的 INTO 子句出现在所有常规子句之后。

Here the INTO clause appears after all the normal clauses.

36.4.4. Type Mapping #

当 ECPG 应用程序在 PostgreSQL 服务器和 C 应用程序之间交换值时（例如，在从服务器检索查询结果或使用输入参数执行 SQL 语句时），需要在 PostgreSQL 数据类型和主机语言变量类型（具体而言是 C 语言数据类型）之间转换值。ECPG 的主要功能之一是，在大多数情况下，它会自动执行此操作。

When ECPG applications exchange values between the PostgreSQL server and the C application, such as when retrieving query results from the server or executing SQL statements with input parameters, the values need to be converted between PostgreSQL data types and host language variable types (C language data types, concretely). One of the main points of ECPG is that it takes care of this automatically in most cases.

从这方面而言，有两种数据类型：一些简单的 PostgreSQL 数据类型，如 integer_和 _text，可以被应用程序直接读写。其他 PostgreSQL 数据类型，如 timestamp_和 _numeric，只能通过特殊库函数访问；请参阅 Section 36.4.4.2。

In this respect, there are two kinds of data types: Some simple PostgreSQL data types, such as integer and text, can be read and written by the application directly. Other PostgreSQL data types, such as timestamp and numeric can only be accessed through special library functions; see Section 36.4.4.2.

Table 36.1显示了哪些 PostgreSQL 数据类型对应哪些 C 数据类型。当你要发送或接收指定 PostgreSQL 数据类型的值时，应在 declare 部分中声明对应 C 数据类型的一个 C 变量。

Table 36.1 shows which PostgreSQL data types correspond to which C data types. When you wish to send or receive a value of a given PostgreSQL data type, you should declare a C variable of the corresponding C data type in the declare section.

Table 36.1. Mapping Between PostgreSQL Data Types and C Variable Types

PostgreSQL data type

Host variable type

smallint

short

integer

int

bigint

long long int

decimal

_decimal_#ftn.ECPG-DATATYPE-TABLE-FN

numeric

_numeric_ecpg-variables.html#ftn.ECPG-DATATYPE-TABLE-FN

real

float

double precision

double

smallserial

short

serial

int

bigserial

long long int

oid

unsigned int

character(_n), _varchar(_n), _text

char[_n+1], _VARCHAR[_n+1]_

name

char[NAMEDATALEN]

timestamp

_timestamp_ecpg-variables.html#ftn.ECPG-DATATYPE-TABLE-FN

interval

_interval_ecpg-variables.html#ftn.ECPG-DATATYPE-TABLE-FN

date

_date_ecpg-variables.html#ftn.ECPG-DATATYPE-TABLE-FN

boolean

_bool_#ftn.id-1.7.5.10.7.5.2.2.17.2.2

bytea

char *, bytea[_n]_

[id="a",role="bare"]#ECPG-DATATYPE-TABLE-FN [id="a"] 只能通过特殊库函数访问此类型；请参阅 Section 36.4.4.2 。

#ECPG-DATATYPE-TABLE-FN This type can only be accessed through special library functions; see Section 36.4.4.2.

[id="b",role="bare"]#id-1.7.5.10.7.5.2.2.17.2.2 [id="b"] 在 ecpglib.h 中声明（如果非本机）

#id-1.7.5.10.7.5.2.2.17.2.2 declared in ecpglib.h if not native

36.4.4.1. Handling Character Strings #

为了处理 SQL 字符串数据类型（例如 varchar 和 text），有两种可能的方法来声明主机变量。

To handle SQL character string data types, such as varchar and text, there are two possible ways to declare the host variables.

一种方法是使用 char[]，即 char 数组，这是 C 中处理字符数据的最常见方法。

One way is using char[], an array of char, which is the most common way to handle character data in C.

EXEC SQL BEGIN DECLARE SECTION;
    char str[50];
EXEC SQL END DECLARE SECTION;

请注意，您必须自己处理长度。如果您将此主机变量用作返回超过 49 个字符的字符串的查询的目标变量，将会发生缓冲区溢出。

Note that you have to take care of the length yourself. If you use this host variable as the target variable of a query which returns a string with more than 49 characters, a buffer overflow occurs.

另一种方法是使用 VARCHAR 类型，这是 ECPG 提供的一种特殊类型。VARCHAR 类型数组的定义会针对每个变量被转换为一个命名的 struct。如下声明：

The other way is using the VARCHAR type, which is a special type provided by ECPG. The definition on an array of type VARCHAR is converted into a named struct for every variable. A declaration like:

VARCHAR var[180];

会被转换为：

is converted into:

struct varchar_var { int len; char arr[180]; } var;

成员 arr 托管包括终止零字节在内的字符串。因此，若要将字符串存储在 VARCHAR 主机变量中，则必须使用包括零字节终止符的长度声明主机变量。成员 len 保存存储在 arr 中的字符串的长度（不包括终止零字节）。当主机变量用作查询的输入时，如果 strlen(arr) 和 len 不同，则使用较短的那个。

The member arr hosts the string including a terminating zero byte. Thus, to store a string in a VARCHAR host variable, the host variable has to be declared with the length including the zero byte terminator. The member len holds the length of the string stored in the arr without the terminating zero byte. When a host variable is used as input for a query, if strlen(arr) and len are different, the shorter one is used.

VARCHAR 可以用大写或小写书写，但不能用混合大小写。

VARCHAR can be written in upper or lower case, but not in mixed case.

char 和 VARCHAR 主机变量还可以保存其他 SQL 类型的值，这些值将以字符串形式存储。

char and VARCHAR host variables can also hold values of other SQL types, which will be stored in their string forms.

36.4.4.2. Accessing Special Data Types #

ECPG 包含一些特殊类型，可帮助你轻松地与 PostgreSQL 服务器中的某些特殊数据类型进行交互。特别是，它已经针对 numeric、decimal、date、timestamp 和 interval 类型实现了支持。这些数据类型不能有效地映射到原始主机变量类型（例如 int、long long int 或 char[]），因为它们具有复杂的内部结构。应用程序通过在特殊类型中声明主机变量并使用 pgtypes 库中的函数来访问它们来处理这些类型。在 Section 36.6 中详细描述的 pgtypes 库包含处理这些类型的基本函数，这样你无需向 SQL 服务器发送查询即可将时间间隔添加到时间戳，例如。

ECPG contains some special types that help you to interact easily with some special data types from the PostgreSQL server. In particular, it has implemented support for the numeric, decimal, date, timestamp, and interval types. These data types cannot usefully be mapped to primitive host variable types (such as int, long long int, or char[]), because they have a complex internal structure. Applications deal with these types by declaring host variables in special types and accessing them using functions in the pgtypes library. The pgtypes library, described in detail in Section 36.6 contains basic functions to deal with those types, such that you do not need to send a query to the SQL server just for adding an interval to a time stamp for example.

以下小节介绍了这些特殊数据类型。有关 pgtypes 库函数的更多详细信息，请参阅 Section 36.6。

The follow subsections describe these special data types. For more details about pgtypes library functions, see Section 36.6.

这是在 ECPG 主机应用程序中处理 timestamp 变量的模式。

Here is a pattern for handling timestamp variables in the ECPG host application.

首先，程序必须包含 timestamp 类型的头文件：

First, the program has to include the header file for the timestamp type:

#include <pgtypes_timestamp.h>

接下来，在 declare 部分中将主机变量声明为类型 timestamp：

Next, declare a host variable as type timestamp in the declare section:

EXEC SQL BEGIN DECLARE SECTION;
timestamp ts;
EXEC SQL END DECLARE SECTION;

在将值读入主机变量后，使用 pgtypes 函数库函数对其进行处理。在下例中，将 timestamp 值使用 PGTYPEStimestamp_to_asc() 函数转换为文本 (ASCII) 形式：

And after reading a value into the host variable, process it using pgtypes library functions. In following example, the timestamp value is converted into text (ASCII) form with the PGTYPEStimestamp_to_asc() function:

EXEC SQL SELECT now()::timestamp INTO :ts;

printf("ts = %s\n", PGTYPEStimestamp_to_asc(ts));

此示例将显示如下结果：

This example will show some result like following:

ts = 2010-06-27 18:03:56.949343

此外，DATE 类型可以以相同的方式处理。该程序必须包含 pgtypes_date.h，将一个主机变量声明为日期类型，并使用 PGTYPESdate_to_asc() 函数将一个 DATE 值转换为文本形式。有关 pgtypes 库函数的更多详细信息，请参阅 Section 36.6。

In addition, the DATE type can be handled in the same way. The program has to include pgtypes_date.h, declare a host variable as the date type and convert a DATE value into a text form using PGTYPESdate_to_asc() function. For more details about the pgtypes library functions, see Section 36.6.

interval 类型的处理方式与 timestamp 和 date 类型类似。但是，必须显式分配 interval 类型值内存。换句话说，变量的内存空间必须在堆内存中分配，而不是在栈内存中分配。

The handling of the interval type is also similar to the timestamp and date types. It is required, however, to allocate memory for an interval type value explicitly. In other words, the memory space for the variable has to be allocated in the heap memory, not in the stack memory.

这是一个示例程序：

Here is an example program:

#include <stdio.h>
#include <stdlib.h>
#include <pgtypes_interval.h>

int
main(void)
{
EXEC SQL BEGIN DECLARE SECTION;
    interval *in;
EXEC SQL END DECLARE SECTION;

    EXEC SQL CONNECT TO testdb;
    EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT;

    in = PGTYPESinterval_new();
    EXEC SQL SELECT '1 min'::interval INTO :in;
    printf("interval = %s\n", PGTYPESinterval_to_asc(in));
    PGTYPESinterval_free(in);

    EXEC SQL COMMIT;
    EXEC SQL DISCONNECT ALL;
    return 0;
}

numeric 和 decimal 类型的处理类似于 interval 类型：它需要定义一个指针，在堆上分配一些内存空间，并使用 pgtypes 库函数访问该变量。有关 pgtypes 库函数的更多详细信息，请参阅 Section 36.6。

The handling of the numeric and decimal types is similar to the interval type: It requires defining a pointer, allocating some memory space on the heap, and accessing the variable using the pgtypes library functions. For more details about the pgtypes library functions, see Section 36.6.

没有专门为 decimal 类型提供的函数。应用程序必须使用 pgtypes 函数库函数将其转换为 numeric 变量，以便进一步处理。

No functions are provided specifically for the decimal type. An application has to convert it to a numeric variable using a pgtypes library function to do further processing.

这是一个处理 numeric 和 decimal 类型变量的示例程序。

Here is an example program handling numeric and decimal type variables.

#include <stdio.h>
#include <stdlib.h>
#include <pgtypes_numeric.h>

EXEC SQL WHENEVER SQLERROR STOP;

int
main(void)
{
EXEC SQL BEGIN DECLARE SECTION;
    numeric *num;
    numeric *num2;
    decimal *dec;
EXEC SQL END DECLARE SECTION;

    EXEC SQL CONNECT TO testdb;
    EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT;

    num = PGTYPESnumeric_new();
    dec = PGTYPESdecimal_new();

    EXEC SQL SELECT 12.345::numeric(4,2), 23.456::decimal(4,2) INTO :num, :dec;

    printf("numeric = %s\n", PGTYPESnumeric_to_asc(num, 0));
    printf("numeric = %s\n", PGTYPESnumeric_to_asc(num, 1));
    printf("numeric = %s\n", PGTYPESnumeric_to_asc(num, 2));

    /* Convert decimal to numeric to show a decimal value. */
    num2 = PGTYPESnumeric_new();
    PGTYPESnumeric_from_decimal(dec, num2);

    printf("decimal = %s\n", PGTYPESnumeric_to_asc(num2, 0));
    printf("decimal = %s\n", PGTYPESnumeric_to_asc(num2, 1));
    printf("decimal = %s\n", PGTYPESnumeric_to_asc(num2, 2));

    PGTYPESnumeric_free(num2);
    PGTYPESdecimal_free(dec);
    PGTYPESnumeric_free(num);

    EXEC SQL COMMIT;
    EXEC SQL DISCONNECT ALL;
    return 0;
}

bytea 类型的处理方式类似于 VARCHAR。将类型为 bytea 的数组的定义转换为每个变量的名为 struct。类似如下的声明：

The handling of the bytea type is similar to that of VARCHAR. The definition on an array of type bytea is converted into a named struct for every variable. A declaration like:

bytea var[180];

会被转换为：

is converted into:

struct bytea_var { int len; char arr[180]; } var;

成员 arr 托管二进制格式数据。它还可以处理 '\0' 作为数据的一部分，与 VARCHAR 不同。数据从/转换为十六进制格式，并由 ecpglib 发送/接收。

The member arr hosts binary format data. It can also handle '\0' as part of data, unlike VARCHAR. The data is converted from/to hex format and sent/received by ecpglib.

Note

_bytea_变量只能在将 bytea_output设置为_hex_时使用。

bytea variable can be used only when bytea_output is set to hex.

36.4.4.3. Host Variables with Nonprimitive Types #

作为主机变量，您还可以使用数组、typedef、struct 和指针。

As a host variable you can also use arrays, typedefs, structs, and pointers.

对于数组作为主机变量，有两种用例。第一个是一种将一些文本字符串存储在 char[] 或 VARCHAR[] 中的方法，如 Section 36.4.4.1 中所述。第二个用例是从查询结果中检索多行而不使用游标。如果没有数组，要处理由多行组成的查询结果，则需要使用游标和 FETCH 命令。但使用数组主机变量，可以一次接收多行。必须定义数组的长度以容纳所有行，否则可能会发生缓冲区溢出。

There are two use cases for arrays as host variables. The first is a way to store some text string in char[] or VARCHAR[], as explained in Section 36.4.4.1. The second use case is to retrieve multiple rows from a query result without using a cursor. Without an array, to process a query result consisting of multiple rows, it is required to use a cursor and the FETCH command. But with array host variables, multiple rows can be received at once. The length of the array has to be defined to be able to accommodate all rows, otherwise a buffer overflow will likely occur.

以下示例扫描 pg_database 系统表并显示可用数据库的所有 OID 和名称：

Following example scans the pg_database system table and shows all OIDs and names of the available databases:

int
main(void)
{
EXEC SQL BEGIN DECLARE SECTION;
    int dbid[8];
    char dbname[8][16];
    int i;
EXEC SQL END DECLARE SECTION;

    memset(dbname, 0, sizeof(char)* 16 * 8);
    memset(dbid, 0, sizeof(int) * 8);

    EXEC SQL CONNECT TO testdb;
    EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT;

    /* Retrieve multiple rows into arrays at once. */
    EXEC SQL SELECT oid,datname INTO :dbid, :dbname FROM pg_database;

    for (i = 0; i < 8; i++)
        printf("oid=%d, dbname=%s\n", dbid[i], dbname[i]);

    EXEC SQL COMMIT;
    EXEC SQL DISCONNECT ALL;
    return 0;
}

此示例显示了以下结果。（确切值取决于当地情况。）

This example shows following result. (The exact values depend on local circumstances.)

oid=1, dbname=template1
oid=11510, dbname=template0
oid=11511, dbname=postgres
oid=313780, dbname=testdb
oid=0, dbname=
oid=0, dbname=
oid=0, dbname=

可以使用其成员名称与查询结果的列名称相匹配的结构，一次检索多列。此结构可以在单个主机变量中处理多个列值。

A structure whose member names match the column names of a query result, can be used to retrieve multiple columns at once. The structure enables handling multiple column values in a single host variable.

以下示例从 pg_database 系统表中检索可用数据库的 OID、名称和大小，并使用 pg_database_size() 函数。在此示例中，使用结构变量 dbinfo_t（其成员名称与 SELECT 结果中的每一列匹配）来检索一个结果行，而不会在 FETCH 语句中放入多个主机变量。

The following example retrieves OIDs, names, and sizes of the available databases from the pg_database system table and using the pg_database_size() function. In this example, a structure variable dbinfo_t with members whose names match each column in the SELECT result is used to retrieve one result row without putting multiple host variables in the FETCH statement.

EXEC SQL BEGIN DECLARE SECTION;
    typedef struct
    {
       int oid;
       char datname[65];
       long long int size;
    } dbinfo_t;

    dbinfo_t dbval;
EXEC SQL END DECLARE SECTION;

    memset(&dbval, 0, sizeof(dbinfo_t));

    EXEC SQL DECLARE cur1 CURSOR FOR SELECT oid, datname, pg_database_size(oid) AS size FROM pg_database;
    EXEC SQL OPEN cur1;

    /* when end of result set reached, break out of while loop */
    EXEC SQL WHENEVER NOT FOUND DO BREAK;

    while (1)
    {
        /* Fetch multiple columns into one structure. */
        EXEC SQL FETCH FROM cur1 INTO :dbval;

        /* Print members of the structure. */
        printf("oid=%d, datname=%s, size=%lld\n", dbval.oid, dbval.datname, dbval.size);
    }

    EXEC SQL CLOSE cur1;

此示例显示了以下结果。（确切值取决于当地情况。）

This example shows following result. (The exact values depend on local circumstances.)

oid=1, datname=template1, size=4324580
oid=11510, datname=template0, size=4243460
oid=11511, datname=postgres, size=4324580
oid=313780, datname=testdb, size=8183012

结构主机变量最多“吸收”其作为字段的结构列。其他列可以分配给其他主机变量。例如，上面程序也可以像这样重组，其中 size 变量位于结构之外：

Structure host variables “absorb” as many columns as the structure as fields. Additional columns can be assigned to other host variables. For example, the above program could also be restructured like this, with the size variable outside the structure:

EXEC SQL BEGIN DECLARE SECTION;
    typedef struct
    {
       int oid;
       char datname[65];
    } dbinfo_t;

    dbinfo_t dbval;
    long long int size;
EXEC SQL END DECLARE SECTION;

    memset(&dbval, 0, sizeof(dbinfo_t));

    EXEC SQL DECLARE cur1 CURSOR FOR SELECT oid, datname, pg_database_size(oid) AS size FROM pg_database;
    EXEC SQL OPEN cur1;

    /* when end of result set reached, break out of while loop */
    EXEC SQL WHENEVER NOT FOUND DO BREAK;

    while (1)
    {
        /* Fetch multiple columns into one structure. */
        EXEC SQL FETCH FROM cur1 INTO :dbval, :size;

        /* Print members of the structure. */
        printf("oid=%d, datname=%s, size=%lld\n", dbval.oid, dbval.datname, size);
    }

    EXEC SQL CLOSE cur1;

使用 typedef 关键字将新类型映射到已经存在的类型。

Use the typedef keyword to map new types to already existing types.

EXEC SQL BEGIN DECLARE SECTION;
    typedef char mychartype[40];
    typedef long serial_t;
EXEC SQL END DECLARE SECTION;

请注意，您还可以使用：

Note that you could also use:

EXEC SQL TYPE serial_t IS long;

此声明不必是声明部分的一部分；也就是说，您还可以将类型定义写为普通 C 语句。

This declaration does not need to be part of a declare section; that is, you can also write typedefs as normal C statements.

你在一个程序中声明为 typedef 的任何单词都不可在同一程序中后续的 EXEC SQL 命令中用作 SQL 关键字。例如，以下代码无法正常工作：

Any word you declare as a typedef cannot be used as an SQL keyword in EXEC SQL commands later in the same program. For example, this won’t work:

EXEC SQL BEGIN DECLARE SECTION;
    typedef int start;
EXEC SQL END DECLARE SECTION;
...
EXEC SQL START TRANSACTION;

ECPG 将针对 START TRANSACTION 报告一个语法错误，因为它不再将 START 识别为 SQL 关键字，只识别为 typedef。（如果你遇到了这样的冲突，并且重命名 typedef 似乎不切实际，你可以使用 dynamic SQL 编写 SQL 命令。）

ECPG will report a syntax error for START TRANSACTION, because it no longer recognizes START as an SQL keyword, only as a typedef. (If you have such a conflict, and renaming the typedef seems impractical, you could write the SQL command using dynamic SQL.)

Note

在 v16 之前的 PostgreSQL 版本中，使用 SQL 关键字作为 typedef 名称很可能会导致与 typedef 本身的用法相关的语法错误，而不是将该名称用作 SQL 关键字。当现有的 ECPG 应用程序在具有新关键字的新 PostgreSQL 版本中重新编译时，新行为不太可能造成问题。

In PostgreSQL releases before v16, use of SQL keywords as typedef names was likely to result in syntax errors associated with use of the typedef itself, rather than use of the name as an SQL keyword. The new behavior is less likely to cause problems when an existing ECPG application is recompiled in a new PostgreSQL release with new keywords.

可以声明指向最常见类型的指针。但请注意，不能将指针用作没有自动分配的查询的目标变量。请参阅 Section 36.7 以获取有关自动分配的更多信息。

You can declare pointers to the most common types. Note however that you cannot use pointers as target variables of queries without auto-allocation. See Section 36.7 for more information on auto-allocation.

EXEC SQL BEGIN DECLARE SECTION;
    int   *intp;
    char **charp;
EXEC SQL END DECLARE SECTION;

36.4.5. Handling Nonprimitive SQL Data Types #

此部分包含有关如何在 ECPG 应用程序中处理非标量和用户定义的 SQL 级数据类型的信息。请注意，这与上一部分中描述的处理非基元类型的宿主变量不同。

This section contains information on how to handle nonscalar and user-defined SQL-level data types in ECPG applications. Note that this is distinct from the handling of host variables of nonprimitive types, described in the previous section.

36.4.5.1. Arrays #

ECPG 直接不支持多维 SQL 级数组。一维 SQL 级数组可以被映射到 C 数组宿主变量中，反之亦然。但是，在创建语句时，ecpg 不知道列的类型，因此无法检查 C 数组是否被输入到相应的 SQL 级数组中。在处理 SQL 语句的输出时，ecpg 拥有必要的信息，因此会检查两者是否是数组。

Multi-dimensional SQL-level arrays are not directly supported in ECPG. One-dimensional SQL-level arrays can be mapped into C array host variables and vice-versa. However, when creating a statement ecpg does not know the types of the columns, so that it cannot check if a C array is input into a corresponding SQL-level array. When processing the output of an SQL statement, ecpg has the necessary information and thus checks if both are arrays.

如果一个查询分别访问一个数组的 elements，则这样可以避免在 ECPG 中使用数组。然后，应该使用可以映射到元素类型的一个类型的主机变量。例如，如果一个列类型是 integer 数组，则可以使用类型为 int 的主机变量。同样，如果元素类型是 varchar 或 text，则可以使用类型为 char[] 或 VARCHAR[] 的主机变量。

If a query accesses elements of an array separately, then this avoids the use of arrays in ECPG. Then, a host variable with a type that can be mapped to the element type should be used. For example, if a column type is array of integer, a host variable of type int can be used. Also if the element type is varchar or text, a host variable of type char[] or VARCHAR[] can be used.

这里有一个例子。假设有以下表：

Here is an example. Assume the following table:

CREATE TABLE t3 (
    ii integer[]
);

testdb=> SELECT * FROM t3;
     ii
-------------
 {1,2,3,4,5}
(1 row)

以下示例程序检索数组的第 4 个元素，并将其存储到类型为 int 的宿主变量中：

The following example program retrieves the 4th element of the array and stores it into a host variable of type int:

EXEC SQL BEGIN DECLARE SECTION;
int ii;
EXEC SQL END DECLARE SECTION;

EXEC SQL DECLARE cur1 CURSOR FOR SELECT ii[4] FROM t3;
EXEC SQL OPEN cur1;

EXEC SQL WHENEVER NOT FOUND DO BREAK;

while (1)
{
    EXEC SQL FETCH FROM cur1 INTO :ii ;
    printf("ii=%d\n", ii);
}

EXEC SQL CLOSE cur1;

此示例显示以下结果：

This example shows the following result:

ii=4

要将多个数组元素映射到数组类型宿主变量中的多个元素，数组列的每个元素和宿主变量数组的每个元素都必须单独管理，例如：

To map multiple array elements to the multiple elements in an array type host variables each element of array column and each element of the host variable array have to be managed separately, for example:

EXEC SQL BEGIN DECLARE SECTION;
int ii_a[8];
EXEC SQL END DECLARE SECTION;

EXEC SQL DECLARE cur1 CURSOR FOR SELECT ii[1], ii[2], ii[3], ii[4] FROM t3;
EXEC SQL OPEN cur1;

EXEC SQL WHENEVER NOT FOUND DO BREAK;

while (1)
{
    EXEC SQL FETCH FROM cur1 INTO :ii_a[0], :ii_a[1], :ii_a[2], :ii_a[3];
    ...
}

请再次注意

Note again that

EXEC SQL BEGIN DECLARE SECTION;
int ii_a[8];
EXEC SQL END DECLARE SECTION;

EXEC SQL DECLARE cur1 CURSOR FOR SELECT ii FROM t3;
EXEC SQL OPEN cur1;

EXEC SQL WHENEVER NOT FOUND DO BREAK;

while (1)
{
    /* WRONG */
    EXEC SQL FETCH FROM cur1 INTO :ii_a;
    ...
}

在这种情况下不起作用，因为您无法直接将数组类型列映射到数组宿主变量。

would not work correctly in this case, because you cannot map an array type column to an array host variable directly.

另一个解决方法是将数组存储在类型为 char[] 或 VARCHAR[] 的主机变量中的它们的外部字符串表示中。有关此表示的更多详细信息，请参阅 Section 8.15.2。请注意，这意味着数组不能在主机程序中作为一个数组被自然地访问（如果没有进一步处理解析文本表示的话）。

Another workaround is to store arrays in their external string representation in host variables of type char[] or VARCHAR[]. For more details about this representation, see Section 8.15.2. Note that this means that the array cannot be accessed naturally as an array in the host program (without further processing that parses the text representation).

36.4.5.2. Composite Types #

ECPG 直接不支持复合类型，但可以轻松解决。可用的解决办法类似于上面为数组描述的解决办法：单独访问每个属性或使用外部字符串表示形式。

Composite types are not directly supported in ECPG, but an easy workaround is possible. The available workarounds are similar to the ones described for arrays above: Either access each attribute separately or use the external string representation.

对于下面的示例，假设以下类型和表格：

For the following examples, assume the following type and table:

CREATE TYPE comp_t AS (intval integer, textval varchar(32));
CREATE TABLE t4 (compval comp_t);
INSERT INTO t4 VALUES ( (256, 'PostgreSQL') );

最显而易见的解决方案是分别访问每一个属性。下面的程序通过分别选择 type comp_t 的每一个属性从示例表格中检索数据：

The most obvious solution is to access each attribute separately. The following program retrieves data from the example table by selecting each attribute of the type comp_t separately:

EXEC SQL BEGIN DECLARE SECTION;
int intval;
varchar textval[33];
EXEC SQL END DECLARE SECTION;

/* Put each element of the composite type column in the SELECT list. */
EXEC SQL DECLARE cur1 CURSOR FOR SELECT (compval).intval, (compval).textval FROM t4;
EXEC SQL OPEN cur1;

EXEC SQL WHENEVER NOT FOUND DO BREAK;

while (1)
{
    /* Fetch each element of the composite type column into host variables. */
    EXEC SQL FETCH FROM cur1 INTO :intval, :textval;

    printf("intval=%d, textval=%s\n", intval, textval.arr);
}

EXEC SQL CLOSE cur1;

为了增强此示例，可以将用于在 FETCH 命令中存储值的主机变量收集到一个结构中。有关结构形式的主机变量的更多详细信息，请参阅 Section 36.4.4.3.2。为了切换到该结构，可以对示例进行如下修改。两个主机变量 intval 和 textval 成为 comp_t 结构的成员，并且在 FETCH 命令中指定了该结构。

To enhance this example, the host variables to store values in the FETCH command can be gathered into one structure. For more details about the host variable in the structure form, see Section 36.4.4.3.2. To switch to the structure, the example can be modified as below. The two host variables, intval and textval, become members of the comp_t structure, and the structure is specified on the FETCH command.

EXEC SQL BEGIN DECLARE SECTION;
typedef struct
{
    int intval;
    varchar textval[33];
} comp_t;

comp_t compval;
EXEC SQL END DECLARE SECTION;

/* Put each element of the composite type column in the SELECT list. */
EXEC SQL DECLARE cur1 CURSOR FOR SELECT (compval).intval, (compval).textval FROM t4;
EXEC SQL OPEN cur1;

EXEC SQL WHENEVER NOT FOUND DO BREAK;

while (1)
{
    /* Put all values in the SELECT list into one structure. */
    EXEC SQL FETCH FROM cur1 INTO :compval;

    printf("intval=%d, textval=%s\n", compval.intval, compval.textval.arr);
}

EXEC SQL CLOSE cur1;

虽然在 FETCH 命令中使用了结构，但是 SELECT 子句中的属性名称逐一指定。通过使用 * 要求复合类型值的全部属性，可以增强此功能。

Although a structure is used in the FETCH command, the attribute names in the SELECT clause are specified one by one. This can be enhanced by using a * to ask for all attributes of the composite type value.

...
EXEC SQL DECLARE cur1 CURSOR FOR SELECT (compval).* FROM t4;
EXEC SQL OPEN cur1;

EXEC SQL WHENEVER NOT FOUND DO BREAK;

while (1)
{
    /* Put all values in the SELECT list into one structure. */
    EXEC SQL FETCH FROM cur1 INTO :compval;

    printf("intval=%d, textval=%s\n", compval.intval, compval.textval.arr);
}
...

通过这种方式，复合类型可以几乎无缝地映射成结构，即使 ECPG 并不理解复合类型本身。

This way, composite types can be mapped into structures almost seamlessly, even though ECPG does not understand the composite type itself.

最后，还可以将复合类型值以它们的外部字符串表示存储在类型为 char[] 或 VARCHAR[] 的主机变量中。但这样，很难从主机程序中访问该值字段。

Finally, it is also possible to store composite type values in their external string representation in host variables of type char[] or VARCHAR[]. But that way, it is not easily possible to access the fields of the value from the host program.

36.4.5.3. User-Defined Base Types #

ECPG 不直接支持新的用户定义基本类型。可以使用类型为 char[] 或 VARCHAR[] 的外部字符串表示和主机变量，而且此解决方案对于许多类型来说确实合适且足够。

New user-defined base types are not directly supported by ECPG. You can use the external string representation and host variables of type char[] or VARCHAR[], and this solution is indeed appropriate and sufficient for many types.

这里是一个使用 Section 38.13 中示例中的数据类型 complex 的示例。该类型的外部字符串表示是 (%f,%f)，它在 Section 38.13 中的 complex_in() 和 complex_out() 函数中被定义。以下示例将复杂类型值 (1,1) 和 (3,3) 插入到列 a 和 b 中，并在之后从表中选择它们。

Here is an example using the data type complex from the example in Section 38.13. The external string representation of that type is (%f,%f), which is defined in the functions complex_in() and complex_out() functions in Section 38.13. The following example inserts the complex type values (1,1) and (3,3) into the columns a and b, and select them from the table after that.

EXEC SQL BEGIN DECLARE SECTION;
    varchar a[64];
    varchar b[64];
EXEC SQL END DECLARE SECTION;

    EXEC SQL INSERT INTO test_complex VALUES ('(1,1)', '(3,3)');

    EXEC SQL DECLARE cur1 CURSOR FOR SELECT a, b FROM test_complex;
    EXEC SQL OPEN cur1;

    EXEC SQL WHENEVER NOT FOUND DO BREAK;

    while (1)
    {
        EXEC SQL FETCH FROM cur1 INTO :a, :b;
        printf("a=%s, b=%s\n", a.arr, b.arr);
    }

    EXEC SQL CLOSE cur1;

此示例显示以下结果：

This example shows following result:

a=(1,1), b=(3,3)

另一种解决方法是避免在 ECPG 中直接使用用户定义类型，而创建函数或类型转换，在用户定义类型和 ECPG 可以处理的原始类型之间进行转换。但是，请注意，类型转换（尤其是隐式转换）应非常谨慎地引入到类型系统中。

Another workaround is avoiding the direct use of the user-defined types in ECPG and instead create a function or cast that converts between the user-defined type and a primitive type that ECPG can handle. Note, however, that type casts, especially implicit ones, should be introduced into the type system very carefully.

例如，

For example,

CREATE FUNCTION create_complex(r double, i double) RETURNS complex
LANGUAGE SQL
IMMUTABLE
AS $$ SELECT $1 * complex '(1,0')' + $2 * complex '(0,1)' $$;

在此定义之后，下面的

After this definition, the following

EXEC SQL BEGIN DECLARE SECTION;
double a, b, c, d;
EXEC SQL END DECLARE SECTION;

a = 1;
b = 2;
c = 3;
d = 4;

EXEC SQL INSERT INTO test_complex VALUES (create_complex(:a, :b), create_complex(:c, :d));

与下面的效果相同

has the same effect as

EXEC SQL INSERT INTO test_complex VALUES ('(1,2)', '(3,4)');

36.4.6. Indicators #

上面的示例并未处理空值。实际上，如果从数据库中提取空值，检索示例将会引发错误。要能够将空值传递到数据库或从数据库中检索空值，您需要将第二个宿主变量规范附加到每个包含数据的宿主变量上。此第二个宿主变量被称为 indicator，其中包含一个标记，该标记指示数据是否为 null（在这种情况下，将忽略真实宿主变量的值）。下面是一个正确处理空值检索的示例：

The examples above do not handle null values. In fact, the retrieval examples will raise an error if they fetch a null value from the database. To be able to pass null values to the database or retrieve null values from the database, you need to append a second host variable specification to each host variable that contains data. This second host variable is called the indicator and contains a flag that tells whether the datum is null, in which case the value of the real host variable is ignored. Here is an example that handles the retrieval of null values correctly:

EXEC SQL BEGIN DECLARE SECTION;
VARCHAR val;
int val_ind;
EXEC SQL END DECLARE SECTION:

 ...

EXEC SQL SELECT b INTO :val :val_ind FROM test1;

如果值不为 null，指示变量 val_ind 将为零，如果值为 null，它将为负。（请参阅 Section 36.16 以启用 Oracle 特定的行为。）

The indicator variable val_ind will be zero if the value was not null, and it will be negative if the value was null. (See Section 36.16 to enable Oracle-specific behavior.)

指示器还有另一个功能：如果指示器值为正，则表示该值不为 null，但存储在宿主变量中时被截断了。

The indicator has another function: if the indicator value is positive, it means that the value is not null, but it was truncated when it was stored in the host variable.

如果将 -r no_indicator 参数传递给预处理器 ecpg，它将以“无指示器”模式运行。在无指示器模式下，如果未指定指示器变量，则会将 null 值（在输入和输出中）标记为字符字符串类型的空字符串，并标记为类型可能获得的最低值（例如，对于 int 的 INT_MIN）。

If the argument -r no_indicator is passed to the preprocessor ecpg, it works in “no-indicator” mode. In no-indicator mode, if no indicator variable is specified, null values are signaled (on input and output) for character string types as empty string and for integer types as the lowest possible value for type (for example, INT_MIN for int).