Bulk INSERT can be used to input multiple rows of data into the table using a single ECPG statement that uses the newly introduced 'FOR' clause.
This functionality allows the user to make use of the data stored in host array variables, resulting in 'C' client programs that are simpler and easier to maintain.
Synopsis
The syntax of the bulk INSERT statement is given below:
EXEC SQL [ AT connection ] [ FOR {number_of_rows|ARRAY_SIZE}] INSERT INTO table_name [ ( column_name [, ...] ) ] { VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query } [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] INTO output_host_var [ [ INDICATOR ] indicator_var ] [, ...]]
When the above bulk INSERT command is used, ECPG inserts 'number_of_rows' number of rows into the table, using the data that is stored in the 'expression'.
The 'FOR' clause indicates to ECPG that the given INSERT statement is a bulk insert statement. The 'FOR' clause currently only support INSERT statements. When a 'FOR' clause specified, ECPG executes the INSERT statement for 'number_of_rows' number of times, iterating through the host array variables.
The 'FOR' clause can iterate through all the array elements or can be limited to process only a fixed number of array elements. The value for 'number_of_rows' should be greater than zero.
The 'FOR' clause can accept an integer host variable or an integer literal as its parameter. It can also accept the constant 'ARRAY_SIZE' in which case all the elements in the host array variable are inserted into the table.
Given below are examples of acceptable bulk INSERT statements.
int number_of_rows = 10; int id[25]; char name[25][10]; EXEC SQL FOR :number_of_rows /* will process 10 rows */ INSERT INTO prod (name, id) VALUES (:name, :id); EXEC SQL FOR ARRAY_SIZE /* will process 25 rows */ INSERT INTO prod (name, id) VALUES (:name, :id);
When 'FOR' clause is specified, the 'expression' can be a host array variable, host variable, constant number or string.
The value given for the 'number_of_rows' should be greater than zero. Value which is less than or equal to zero will result in a run-time error.
When 'FOR ARRAY_SIZE' is specified, the values clause should consist of at least one host array variable. 'SELECT' queries cannot be used for input values when using 'FOR ARRAY_SIZE'.
When the value given for the 'number_of_rows' is greater than one, the specified 'SELECT' query should returns only one row. More than one returned row will result in an error.
The VALUES clause includes the input data that is to be inserted into the table. When working with FOR clause, the values in the 'expression' can be host array variables, host variable, constant number, string or pointers.
See
For more detailed usage of the INSERT statement, please refer to the ECOBPG section of the PostgreSQL documentation.
Error Messages
Given below are the error messages that are output when bulk INSERT functionality is not used correctly.
invalid statement name "FOR value should be positive integer"
The value given for number_of_rows is less than or equal to 0.
Specify a value that is more than or equal to 1 for number_of_rows.
invalid statement name "Host array variable is needed when using FOR ARRAY_SIZE"
A host array is not specified in the values clause when using the ARRAY_SIZE keyword.
At least one host array variable should be included in the values clause
SELECT...INTO returns too many rows
The number of rows returned by the 'SELECT ... INTO' query in the INSERT statement is more than one.
When the value of 'number_of_rows' is more than one, the maximum number of rows that can be returned by the 'SELECT ... INTO' query in the INSERT statement is one.
Limitations
The limitations when using bulk INSERT are given below.
Array of structures should not be used as an input in the 'VALUES' clause. Attempted use will result in junk data being inserted into the table.
Array of pointers should not be used as an input in the 'VALUES' clause. Attempted use will result in junk data being inserted into the table.
ECPG supports the use of 'WITH' clause in single INSERT statements. 'WITH' clause cannot be used in bulk INSERT statements.
ECPG does not calculate the size of the pointer variable. So when a pointer variable is used that includes multiple elements, the 'number_of_rows' should be less than or equal to the number of elements in the pointer. Otherwise, junk data will be inserted into the table.
Samples
Given below are some sample usages of the bulk INSERT functionality.
int in_f1[4] = {1,2,3,4};
...
EXEC SQL FOR 3 INSERT INTO target (f1) VALUES (:in_f1);The number of rows to insert indicated by the FOR clause is 3, so the data in the first 3 elements of the host array variable are inserted into the table. The contents of the target table will be:
f1 ---- 1 2 3 (3 rows)
Also a host integer variable can be used to indicate the number of rows that will be inserted in FOR clause, which will produce the same result as above:
int num = 3;
int in_f1[4] = {1,2,3,4};
...
EXEC SQL FOR :num INSERT INTO target (f1) VALUES (:in_f1);Constant values can also be bulk INSERTed into the table as follows:
EXEC SQL FOR 3 INSERT INTO target (f1,f2) VALUES (DEFAULT,'hello');
Assuming the 'DEFAULT' value for the 'f1' column is '0', the contents of the target table will be:
f1 | f2 ---+------- 0 | hello 0 | hello 0 | hello (3 rows)
'FOR ARRAY_SIZE' can be used to insert the entire contents of a host array variable, without explicitly specifying the size, into the table.
int in_f1[4] = {1,2,3,4};
...
EXEC SQL FOR ARRAY_SIZE INSERT INTO target (f1) VALUES (:in_f1);In the above example, four rows are inserted into the table.
Note
If there are multiple host array variables specified as input values, then the number of rows inserted is same as the smallest array size. The example given below demonstrates this usage.
int in_f1[4] = {1,2,3,4};
char in_f3[3][10] = {"one", "two", "three"};
...
EXEC SQL FOR ARRAY_SIZE INSERT INTO target (f1,f3) VALUES (:in_f1,:in_f3);In the above example, the array sizes are 3 and 4. Given that the smallest array size is 3, only three rows are inserted into the table. The table contents are given below.
f1 | f3 ----+------- 1 | one 2 | two 3 | three (3 rows)
Pointers that contain multiple elements can be used in bulk INSERT.
int *in_pf1 = NULL; in_pf1 = (int*)malloc(4*sizeof(int)); in_pf1[0]=1; in_pf1[1]=2; in_pf1[2]=3; in_pf1[3]=4; ... EXEC SQL FOR 4 INSERT INTO target (f1) values (:in_pf1);
The above example will insert four rows into the target table.
When using bulk INSERT, the input values can be got from the results of a SELECT statement. For ex.,
EXEC SQL FOR 4 INSERT INTO target(f1) SELECT age FROM source WHERE name LIKE 'foo';
Assuming that the 'SELECT' query returns one row, the same row will be inserted into the target table four times.
Note
If the 'SELECT' query returns more than one row, the INSERT statement will throw an error.
EXEC SQL FOR 1 INSERT INTO target(f1) SELECT age FROM source;
In the above example, all the rows returned by the 'SELECT' statement will be inserted into the table. In this context '1' has the meaning of 'returned row equivalent'.
Bulk INSERT supports the same RETURNING clause syntax as normal INSERT. An example is given below.
int out_f1[4];
int in_f1[4] = {1,2,3,4};
...
EXEC SQL FOR 3 INSERT INTO target (f1) VALUES (:in_f1) RETURNING f1 INTO :out_f1;After the execution of the above INSERT statement, the 'out_f1' array will have 3 elements with the values of '1','2' and '3'.