PostgreSQL Reference Manual - Volume 2 - Programming Guide by The PostgreSQL Global Development Group Paperback (6"x9"), 408 pages ISBN 0954612035 RRP £19.95 ($34.95) Sales of this book support the PostgreSQL project! Get a printed copy>>>

PostgreSQL Reference Manual Volume 2 Buy the book here! >>> learn more

3.6.3 Different types of host variables

As a host variable you can also use arrays, typedefs, structs and pointers. Moreover there are special types of host variables that exist only in ECPG.

A few examples on host variables:

Arrays

One of the most common uses of an array declaration is probably the allocation of a char array as in

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

Note that you have to take care of the length for 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.

Typedefs

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;

This declaration does not need to be part of a declare section.

Pointers

You can declare pointers to the most common types. Note however that you can not use pointers as target variables of queries without auto-allocation. See section 3.10 Using SQL Descriptor Areas for more information on auto-allocation.

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

Special types of variables

ECPG contains some special types that help you to interact easily with data from the SQL server. For example it has implemented support for the varchar, numeric, date, timestamp, and interval types. section 3.8 pgtypes library 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 timestamp for example. The special 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;

This structure is suitable for interfacing with SQL datums of type varchar.