PostgreSQL 教程

Appearance

36.7. 使用描述符区域# ​

36.7.1. 命名 SQL 描述符区域
36.7.2. SQLDA 描述符区域

SQL 描述符区域是一种更复杂的方法，用于处理SELECT、FETCH或DESCRIBE语句的结果。SQL 描述符区域将一行数据的数据与元数据项组合到一个数据结构中。在执行动态 SQL 语句时，元数据特别有用，因为结果列的性质可能无法预先得知。PostgreSQL 提供了两种使用描述符区域的方法：命名 SQL 描述符区域和 C 结构 SQLDA。

36.7.1. 命名 SQL 描述符区域# ​

命名 SQL 描述符区域包含一个头，其中包含有关整个描述符的信息，以及一个或多个项描述符区域，它们基本上分别描述结果行中的一列。

在使用 SQL 描述符区域之前，需要分配一个

EXEC SQL ALLOCATE DESCRIPTOR identifier;

标识符用作描述符区域的“变量名”。当不再需要描述符时，应取消分配

EXEC SQL DEALLOCATE DESCRIPTOR identifier;

要使用描述符区域，请在INTO子句中将其指定为存储目标，而不是列出主机变量

EXEC SQL FETCH NEXT FROM mycursor INTO SQL DESCRIPTOR mydesc;

如果结果集为空，描述符区域仍将包含查询的元数据，即字段名称。

对于尚未执行的已准备查询，可以使用DESCRIBE语句获取结果集的元数据

EXEC SQL BEGIN DECLARE SECTION;
char *sql_stmt = "SELECT * FROM table1";
EXEC SQL END DECLARE SECTION;

EXEC SQL PREPARE stmt1 FROM :sql_stmt;
EXEC SQL DESCRIBE stmt1 INTO SQL DESCRIPTOR mydesc;

在 PostgreSQL 9.0 之前，SQL关键字是可选的，因此使用DESCRIPTOR和SQL DESCRIPTOR会生成命名的 SQL 描述符区域。现在它是强制性的，省略SQL关键字会生成 SQLDA 描述符区域，请参阅第 36.7.2 节。

在DESCRIBE和FETCH语句中，INTO和USING关键字可以类似地使用：它们在描述符区域中生成结果集和元数据。

现在，如何从描述符区域中获取数据？可以将描述符区域视为具有命名字段的结构。要从头中检索字段的值并将其存储到主机变量中，请使用以下命令

EXEC SQL GET DESCRIPTOR name :hostvar = field;

目前，只定义了一个头字段：COUNT，它表示存在多少个项目描述符区域（即结果中包含多少列）。主机变量需要为整数类型。要从项目描述符区域获取字段，请使用以下命令

EXEC SQL GET DESCRIPTOR name VALUE num :hostvar = field;

*num*可以是文字整数或包含整数的主机变量。可能的字段是

CARDINALITY（整数） #

结果集中的行数

DATA #

实际数据项（因此，此字段的数据类型取决于查询）

DATETIME_INTERVAL_CODE（整数） #

当TYPE 为 9 时，DATETIME_INTERVAL_CODE 的值为 1（表示 DATE）、2（表示 TIME）、3（表示 TIMESTAMP）、4（表示 TIME WITH TIME ZONE）或 5（表示 TIMESTAMP WITH TIME ZONE）。

DATETIME_INTERVAL_PRECISION（整数） #

未实现

INDICATOR（整数） #

指示符（指示空值或值截断）

KEY_MEMBER（整数） #

未实现

LENGTH（整数） #

以字符为单位的数据长度

NAME（字符串） #

列的名称

NULLABLE（整数） #

未实现

OCTET_LENGTH（整数） #

以字节为单位的数据字符表示的长度

PRECISION（整数） #

精度（对于类型 numeric）

RETURNED_LENGTH（整数） #

以字符为单位的数据长度

RETURNED_OCTET_LENGTH（整数） #

以字节为单位的数据字符表示的长度

SCALE（整数） #

刻度（对于类型 numeric）

TYPE（整数） #

列的数据类型的数字代码

在EXECUTE、DECLARE和OPEN语句中，INTO和USING关键字的效果不同。还可以手动构建描述符区域来提供查询或游标的输入参数，USING SQL DESCRIPTOR*name*是将输入参数传递到参数化查询中的方法。构建命名的 SQL 描述符区域的语句如下

EXEC SQL SET DESCRIPTOR name VALUE num field = :hostvar;

PostgreSQL 支持在一条FETCH语句中检索多个记录，并将数据存储在主机变量中，在这种情况下假定变量是一个数组。例如

EXEC SQL BEGIN DECLARE SECTION;
int id[5];
EXEC SQL END DECLARE SECTION;

EXEC SQL FETCH 5 FROM mycursor INTO SQL DESCRIPTOR mydesc;

EXEC SQL GET DESCRIPTOR mydesc VALUE 1 :id = DATA;

36.7.2. SQLDA 描述符区域# ​

SQLDA 描述符区域是一个 C 语言结构，也可以用于获取查询的结果集和元数据。一个结构存储结果集中的一个记录。

EXEC SQL include sqlda.h;
sqlda_t         *mysqlda;

EXEC SQL FETCH 3 FROM mycursor INTO DESCRIPTOR mysqlda;

请注意，SQL关键字已省略。关于INTO和USING关键字在第 36.7.1 节中用例的段落也适用于此处，并进行了补充。在DESCRIBE语句中，如果使用了INTO关键字，则可以完全省略DESCRIPTOR关键字

EXEC SQL DESCRIBE prepared_statement INTO mysqlda;

使用 SQLDA 的程序的一般流程是

  1. 准备查询，并为其声明一个游标。

  2. 为结果行声明一个 SQLDA。

  3. 为输入参数声明一个 SQLDA，并对其进行初始化（内存分配、参数设置）。

  4. 使用输入 SQLDA 打开一个游标。

  5. 从游标中获取行，并将它们存储到输出 SQLDA 中。

  6. 将输出 SQLDA 中的值读入主机变量（必要时进行转换）。

  7. 关闭游标。

  8. 释放为输入 SQLDA 分配的内存区域。

36.7.2.1. SQLDA 数据结构# ​

SQLDA 使用三种数据结构类型：sqlda_t、sqlvar_t和struct sqlname。

提示

PostgreSQL 的 SQLDA 具有与 IBM DB2 Universal Database 中类似的数据结构，因此有关 DB2 的 SQLDA 的一些技术信息可能有助于更好地理解 PostgreSQL 的 SQLDA。

36.7.2.1.1. sqlda_t 结构# ​

结构类型sqlda_t是实际 SQLDA 的类型。它保存一条记录。并且两个或更多个sqlda_t结构可以通过desc_next字段中的指针连接到一个链表中，从而表示一个有序的行集合。因此，当获取两行或更多行时，应用程序可以通过遵循每个sqlda_t节点中的desc_next指针来读取它们。

sqlda_t的定义是

struct sqlda_struct
{
    char            sqldaid[8];
    long            sqldabc;
    short           sqln;
    short           sqld;
    struct sqlda_struct *desc_next;
    struct sqlvar_struct sqlvar[1];
};

typedef struct sqlda_struct sqlda_t;

字段的含义是

sqldaid #

它包含字面字符串 "SQLDA "。

sqldabc #

它包含已分配空间的大小（以字节为单位）。

sqln #

它包含使用 USING 关键字传递到 OPEN、DECLARE 或 EXECUTE 语句中的参数化查询的输入参数数量。如果将其用作 SELECT、EXECUTE 或 FETCH 语句的输出，则其值与 sqld 语句相同

sqld #

它包含结果集中的字段数量。

desc_next #

如果查询返回多条记录，则返回多个链接的 SQLDA 结构，并且 desc_next 保存对列表中下一个条目的指针。

sqlvar #

这是结果集中列的数组。

36.7.2.1.2. sqlvar_t 结构# ​

结构类型sqlvar_t保存列值和元数据，例如类型和长度。类型的定义是

struct sqlvar_struct
{
    short          sqltype;
    short          sqllen;
    char          *sqldata;
    short         *sqlind;
    struct sqlname sqlname;
};

typedef struct sqlvar_struct sqlvar_t;

字段的含义是

sqltype #

包含字段的类型标识符。对于值，请参阅 ecpgtype.h 中的 enum ECPGttype。

sqllen #

包含字段的二进制长度。例如，对于 ECPGt_int 为 4 字节。

sqldata #

指向数据。数据的格式在 第 36.4.4 节 中进行了描述。

sqlind #

指向空值指示符。0 表示非空，-1 表示空。

sqlname #

字段的名称。

36.7.2.1.3. struct sqlname 结构# ​

一个struct sqlname结构保存一个列名。它用作sqlvar_t结构的成员。结构的定义是

#define NAMEDATALEN 64

struct sqlname
{
        short           length;
        char            data[NAMEDATALEN];
};

字段的含义是

length #

包含字段名称的长度。

data #

包含实际的字段名称。

36.7.2.2. 使用 SQLDA 检索结果集# ​

通过 SQLDA 检索查询结果集的一般步骤是

  1. 声明一个 sqlda_t 结构来接收结果集。

  2. 执行 FETCH/EXECUTE/DESCRIBE 命令来处理指定已声明 SQLDA 的查询。

  3. 通过查看 sqlda_t 结构的成员 sqln 来检查结果集中记录的数量。

  4. 从 sqlvar[0]、sqlvar[1] 等 sqlda_t 结构的成员获取每列的值。

  5. 通过遵循 sqlda_t 结构的成员 desc_next 指针，转到下一行 (sqlda_t 结构)。

  6. 根据需要重复上述步骤。

以下是一个通过 SQLDA 检索结果集的示例。

首先，声明一个sqlda_t结构来接收结果集。

sqlda_t *sqlda1;

接下来，在命令中指定 SQLDA。这是一个FETCH命令示例。

EXEC SQL FETCH NEXT FROM cur1 INTO DESCRIPTOR sqlda1;

运行一个循环，遵循链表以检索行。

sqlda_t *cur_sqlda;

for (cur_sqlda = sqlda1;
     cur_sqlda != NULL;
     cur_sqlda = cur_sqlda->desc_next)
{
    ...
}

在循环内部，运行另一个循环以检索行的每个列数据 (sqlvar_t结构)。

for (i = 0; i < cur_sqlda->sqld; i++)
{
    sqlvar_t v = cur_sqlda->sqlvar[i];
    char *sqldata = v.sqldata;
    short sqllen  = v.sqllen;
    ...
}

要获取列值，请检查sqltype值，它是sqlvar_t结构的成员。然后，根据列类型切换到适当的方式，将数据从sqlvar字段复制到主机变量。

char var_buf[1024];

switch (v.sqltype)
{
    case ECPGt_char:
        memset(&var_buf, 0, sizeof(var_buf));
        memcpy(&var_buf, sqldata, (sizeof(var_buf) <= sqllen ? sizeof(var_buf) - 1 : sqllen));
        break;

    case ECPGt_int: /* integer */
        memcpy(&intval, sqldata, sqllen);
        snprintf(var_buf, sizeof(var_buf), "%d", intval);
        break;

    ...
}

36.7.2.3. 使用 SQLDA 传递查询参数# ​

使用 SQLDA 将输入参数传递给已准备查询的一般步骤是

  1. 创建已准备查询（已准备语句）

  2. 将 sqlda_t 结构声明为输入 SQLDA。

  3. 为输入 SQLDA 分配内存区域（作为 sqlda_t 结构）。

  4. 在已分配的内存中设置（复制）输入值。

  5. 使用指定输入 SQLDA 打开游标。

以下是一个示例。

首先，创建一个已准备语句。

EXEC SQL BEGIN DECLARE SECTION;
char query[1024] = "SELECT d.oid, * FROM pg_database d, pg_stat_database s WHERE d.oid = s.datid AND (d.datname = ? OR d.oid = ?)";
EXEC SQL END DECLARE SECTION;

EXEC SQL PREPARE stmt1 FROM :query;

接下来，为 SQLDA 分配内存，并在sqln中设置输入参数的数量，它是sqlda_t结构的成员变量。当已准备查询需要两个或更多输入参数时，应用程序必须分配额外的内存空间，其计算方法为（参数数 - 1）* sizeof(sqlvar_t)。此处所示的示例为两个输入参数分配内存空间。

sqlda_t *sqlda2;

sqlda2 = (sqlda_t *) malloc(sizeof(sqlda_t) + sizeof(sqlvar_t));
memset(sqlda2, 0, sizeof(sqlda_t) + sizeof(sqlvar_t));

sqlda2->sqln = 2; /* number of input variables */

在分配内存后，将参数值存储到sqlvar[]数组中。（当 SQLDA 接收结果集时，这是用于检索列值的同一数组。）在此示例中，输入参数为"postgres"（具有字符串类型）和1（具有整数类型）。

sqlda2->sqlvar[0].sqltype = ECPGt_char;
sqlda2->sqlvar[0].sqldata = "postgres";
sqlda2->sqlvar[0].sqllen  = 8;

int intval = 1;
sqlda2->sqlvar[1].sqltype = ECPGt_int;
sqlda2->sqlvar[1].sqldata = (char *) &intval;
sqlda2->sqlvar[1].sqllen  = sizeof(intval);

通过打开游标并指定事先设置的 SQLDA，将输入参数传递给已准备语句。

EXEC SQL OPEN cur1 USING DESCRIPTOR sqlda2;

最后，在使用输入 SQLDA 之后，必须显式释放已分配的内存空间，这与用于接收查询结果的 SQLDA 不同。

free(sqlda2);

36.7.2.4. 使用 SQLDA 的示例应用程序# ​

以下是一个示例程序，它描述了如何从系统目录中获取由输入参数指定的数据库的访问统计信息。

此应用程序在数据库 OID 上联接两个系统表 pg_database 和 pg_stat_database，并获取和显示由两个输入参数（数据库postgres和 OID1）检索的数据库统计信息。

首先，为输入声明一个 SQLDA，为输出声明一个 SQLDA。

EXEC SQL include sqlda.h;

sqlda_t *sqlda1; /* an output descriptor */
sqlda_t *sqlda2; /* an input descriptor  */

接下来，连接到数据库，准备一个语句，并为已准备语句声明一个游标。

int
main(void)
{
    EXEC SQL BEGIN DECLARE SECTION;
    char query[1024] = "SELECT d.oid,* FROM pg_database d, pg_stat_database s WHERE d.oid=s.datid AND ( d.datname=? OR d.oid=? )";
    EXEC SQL END DECLARE SECTION;

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

    EXEC SQL PREPARE stmt1 FROM :query;
    EXEC SQL DECLARE cur1 CURSOR FOR stmt1;

接下来，将一些值放入输入 SQLDA 以供输入参数使用。为输入 SQLDA 分配内存，并将输入参数的数量设置为sqln。将类型、值和值长度存储到sqltype、sqldata和sqllen中，位于sqlvar结构中。

/* Create SQLDA structure for input parameters. */
    sqlda2 = (sqlda_t *) malloc(sizeof(sqlda_t) + sizeof(sqlvar_t));
    memset(sqlda2, 0, sizeof(sqlda_t) + sizeof(sqlvar_t));
    sqlda2->sqln = 2; /* number of input variables */

    sqlda2->sqlvar[0].sqltype = ECPGt_char;
    sqlda2->sqlvar[0].sqldata = "postgres";
    sqlda2->sqlvar[0].sqllen  = 8;

    intval = 1;
    sqlda2->sqlvar[1].sqltype = ECPGt_int;
    sqlda2->sqlvar[1].sqldata = (char *)&intval;
    sqlda2->sqlvar[1].sqllen  = sizeof(intval);

设置输入 SQLDA 后，使用输入 SQLDA 打开一个游标。

/* Open a cursor with input parameters. */
    EXEC SQL OPEN cur1 USING DESCRIPTOR sqlda2;

从打开的游标中提取行到输出 SQLDA。（通常，您必须在循环中重复调用FETCH，以提取结果集中的所有行。）

while (1)
    {
        sqlda_t *cur_sqlda;

        /* Assign descriptor to the cursor  */
        EXEC SQL FETCH NEXT FROM cur1 INTO DESCRIPTOR sqlda1;

接下来，通过遵循sqlda_t结构的链接列表，从 SQLDA 中检索提取的记录。

for (cur_sqlda = sqlda1 ;
         cur_sqlda != NULL ;
         cur_sqlda = cur_sqlda->desc_next)
    {
        ...

读取第一条记录中的每一列。列数存储在sqld中，第一列的实际数据存储在sqlvar[0]中，两者都是sqlda_t结构的成员。

/* Print every column in a row. */
        for (i = 0; i < sqlda1->sqld; i++)
        {
            sqlvar_t v = sqlda1->sqlvar[i];
            char *sqldata = v.sqldata;
            short sqllen  = v.sqllen;

            strncpy(name_buf, v.sqlname.data, v.sqlname.length);
            name_buf[v.sqlname.length] = '\0';

现在，列数据存储在变量v中。将每个数据项复制到主机变量中，查看v.sqltype以获取列的类型。

switch (v.sqltype) {
                int intval;
                double doubleval;
                unsigned long long int longlongval;

                case ECPGt_char:
                    memset(&var_buf, 0, sizeof(var_buf));
                    memcpy(&var_buf, sqldata, (sizeof(var_buf) <= sqllen ? sizeof(var_buf)-1 : sqllen));
                    break;

                case ECPGt_int: /* integer */
                    memcpy(&intval, sqldata, sqllen);
                    snprintf(var_buf, sizeof(var_buf), "%d", intval);
                    break;

                ...

                default:
                    ...
            }

            printf("%s = %s (type: %d)\n", name_buf, var_buf, v.sqltype);
        }

处理完所有记录后关闭游标，并断开与数据库的连接。

EXEC SQL CLOSE cur1;
    EXEC SQL COMMIT;

    EXEC SQL DISCONNECT ALL;

整个程序显示在示例 36.1中。

示例 36.1。示例 SQLDA 程序

#include <stdlib.h>
#include <string.h>
#include <stdlib.h>
#include <stdio.h>
#include <unistd.h>

EXEC SQL include sqlda.h;

sqlda_t *sqlda1; /* descriptor for output */
sqlda_t *sqlda2; /* descriptor for input */

EXEC SQL WHENEVER NOT FOUND DO BREAK;
EXEC SQL WHENEVER SQLERROR STOP;

int
main(void)
{
    EXEC SQL BEGIN DECLARE SECTION;
    char query[1024] = "SELECT d.oid,* FROM pg_database d, pg_stat_database s WHERE d.oid=s.datid AND ( d.datname=? OR d.oid=? )";

    int intval;
    unsigned long long int longlongval;
    EXEC SQL END DECLARE SECTION;

    EXEC SQL CONNECT TO uptimedb AS con1 USER uptime;
    EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT;

    EXEC SQL PREPARE stmt1 FROM :query;
    EXEC SQL DECLARE cur1 CURSOR FOR stmt1;

    /* Create an SQLDA structure for an input parameter */
    sqlda2 = (sqlda_t *)malloc(sizeof(sqlda_t) + sizeof(sqlvar_t));
    memset(sqlda2, 0, sizeof(sqlda_t) + sizeof(sqlvar_t));
    sqlda2->sqln = 2; /* a number of input variables */

    sqlda2->sqlvar[0].sqltype = ECPGt_char;
    sqlda2->sqlvar[0].sqldata = "postgres";
    sqlda2->sqlvar[0].sqllen  = 8;

    intval = 1;
    sqlda2->sqlvar[1].sqltype = ECPGt_int;
    sqlda2->sqlvar[1].sqldata = (char *) &intval;
    sqlda2->sqlvar[1].sqllen  = sizeof(intval);

    /* Open a cursor with input parameters. */
    EXEC SQL OPEN cur1 USING DESCRIPTOR sqlda2;

    while (1)
    {
        sqlda_t *cur_sqlda;

        /* Assign descriptor to the cursor  */
        EXEC SQL FETCH NEXT FROM cur1 INTO DESCRIPTOR sqlda1;

        for (cur_sqlda = sqlda1 ;
             cur_sqlda != NULL ;
             cur_sqlda = cur_sqlda->desc_next)
        {
            int i;
            char name_buf[1024];
            char var_buf[1024];

            /* Print every column in a row. */
            for (i=0 ; i<cur_sqlda->sqld ; i++)
            {
                sqlvar_t v = cur_sqlda->sqlvar[i];
                char *sqldata = v.sqldata;
                short sqllen  = v.sqllen;

                strncpy(name_buf, v.sqlname.data, v.sqlname.length);
                name_buf[v.sqlname.length] = '\0';

                switch (v.sqltype)
                {
                    case ECPGt_char:
                        memset(&var_buf, 0, sizeof(var_buf));
                        memcpy(&var_buf, sqldata, (sizeof(var_buf)<=sqllen ? sizeof(var_buf)-1 : sqllen) );
                        break;

                    case ECPGt_int: /* integer */
                        memcpy(&intval, sqldata, sqllen);
                        snprintf(var_buf, sizeof(var_buf), "%d", intval);
                        break;

                    case ECPGt_long_long: /* bigint */
                        memcpy(&longlongval, sqldata, sqllen);
                        snprintf(var_buf, sizeof(var_buf), "%lld", longlongval);
                        break;

                    default:
                    {
                        int i;
                        memset(var_buf, 0, sizeof(var_buf));
                        for (i = 0; i < sqllen; i++)
                        {
                            char tmpbuf[16];
                            snprintf(tmpbuf, sizeof(tmpbuf), "%02x ", (unsigned char) sqldata[i]);
                            strncat(var_buf, tmpbuf, sizeof(var_buf));
                        }
                    }
                        break;
                }

                printf("%s = %s (type: %d)\n", name_buf, var_buf, v.sqltype);
            }

            printf("\n");
        }
    }

    EXEC SQL CLOSE cur1;
    EXEC SQL COMMIT;

    EXEC SQL DISCONNECT ALL;

    return 0;
}

此示例的输出应类似于以下内容（某些数字会有所不同）。

oid = 1 (type: 1)
datname = template1 (type: 1)
datdba = 10 (type: 1)
encoding = 0 (type: 5)
datistemplate = t (type: 1)
datallowconn = t (type: 1)
datconnlimit = -1 (type: 5)
datfrozenxid = 379 (type: 1)
dattablespace = 1663 (type: 1)
datconfig =  (type: 1)
datacl = {=c/uptime,uptime=CTc/uptime} (type: 1)
datid = 1 (type: 1)
datname = template1 (type: 1)
numbackends = 0 (type: 5)
xact_commit = 113606 (type: 9)
xact_rollback = 0 (type: 9)
blks_read = 130 (type: 9)
blks_hit = 7341714 (type: 9)
tup_returned = 38262679 (type: 9)
tup_fetched = 1836281 (type: 9)
tup_inserted = 0 (type: 9)
tup_updated = 0 (type: 9)
tup_deleted = 0 (type: 9)

oid = 11511 (type: 1)
datname = postgres (type: 1)
datdba = 10 (type: 1)
encoding = 0 (type: 5)
datistemplate = f (type: 1)
datallowconn = t (type: 1)
datconnlimit = -1 (type: 5)
datfrozenxid = 379 (type: 1)
dattablespace = 1663 (type: 1)
datconfig =  (type: 1)
datacl =  (type: 1)
datid = 11511 (type: 1)
datname = postgres (type: 1)
numbackends = 0 (type: 5)
xact_commit = 221069 (type: 9)
xact_rollback = 18 (type: 9)
blks_read = 1176 (type: 9)
blks_hit = 13943750 (type: 9)
tup_returned = 77410091 (type: 9)
tup_fetched = 3253694 (type: 9)
tup_inserted = 0 (type: 9)
tup_updated = 0 (type: 9)
tup_deleted = 0 (type: 9)