Host Variables with Non-Primitive Types

Non-primitive host variables can be arrays, typedefs, structures, and pointers.

• Arrays

  There are two use cases for arrays as host variables. The first case is to store some text strings in char[] or VARCHAR[]. The second 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 occur.

  For example, scan the pg_database system catalog and display the OIDs and names of all available databases.

  #include <stdlib.h>
  #include <stdio.h>
  #include <string.h>
  
  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);
      * Connect to the testdb database. The testdb database must be created in advance. */
      EXEC SQL CONNECT TO testdb; 
      /* Retrieve multiple rows to arrays at a time. */
      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; 
  }

  Example output (the exact value depends on the local environment):

  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=

• Structures

  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.

  The following example retrieves OIDs, names, and sizes of the available databases from the pg_database system catalog 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;
  
      /* Exit the while loop when the end of the result set is reached. */
      EXEC SQL WHENEVER NOT FOUND DO BREAK;
  
      while (1)
      {         
          /* Fetch multiple columns into one structure. */
          EXEC SQL FETCH FROM cur1 INTO :dbval;          
          /* Print the members of the structure. */
          printf("oid=%d, datname=%s, size=%lld\n", dbval.oid, dbval.datname, dbval.size);     
      }
      EXEC SQL CLOSE cur1;

  Example output (the exact value depends on the local environment):

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

  Structure host variables "absorb" as many columns in the query result as the structure columns. Additional columns can be allocated to other host variables. 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;      
  
      /* Exit the while loop when the end of the result set is reached. */
      EXEC SQL WHENEVER NOT FOUND DO BREAK;      
      while (1)     
      {         
          /* Fetch multiple columns into one structure. */
          EXEC SQL FETCH FROM cur1 INTO :dbval, :size;          
          /* Print the 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 existing types.

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

  You can also run the following command:

  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.

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