Insert array with PostgreSQL ECPG

Question

I am using ECPG with PostgreSQL 8.4. I have a function which takes a struct containing pointers to a couple of dynamically allocated, "null"-terminated arrays of integers (I have defined null in this case to be -1, as there is no need for negative values, as in

#define INT_ARR_NULL -1

struct foobar {
    int * foo;
    int * bar;
};

void init_foobar( struct foobar * fb ) {
    fb->foo = (int*) calloc( 11, sizeof(int) );
    fb->bar = (int*) calloc( 11, sizeof(int) );
    fb->foo[10]=INT_ARR_NULL;
    fb->bar[10]=INT_ARR_NULL;
    int i;
    for( i = 0; i < 10; ++i ) {
        fb->foo[i] = i;
        fb->bar[i] = i;
    }
}

void print_foo( struct foobar * fb ) {
    int * cursor = fb->foo;
    while( *cursor != INT_ARR_NULL ) {
        printf( "%d ", *cursor );
        ++cursor;
    }
}

I included the print_foo function to show how the array would normally work. If I want to insert these values into an array in a PostgreSQL column using ECPG, how would I go about this?

Answer 1

You should be able to get something working from this example, taken from the ECPG test suite and cut down a bit. I'm unsure if it works with dynamically sized arrays, but this should at least give you a starting point.

main (void)
{
EXEC SQL BEGIN DECLARE SECTION;
    int a[10] = {9,8,7,6,5,4,3,2,1,0};
    char text[25] = "klmnopqrst";
EXEC SQL END DECLARE SECTION;

    EXEC SQL END DECLARE SECTION;

    EXEC SQL CONNECT TO REGRESSDB1;

    EXEC SQL SET AUTOCOMMIT = ON;

    EXEC SQL BEGIN WORK;

    EXEC SQL CREATE TABLE test (f float, i int, a int[10], text char(10));

    EXEC SQL INSERT INTO test(f,i,a,text) VALUES(404.90,3,'{0,1,2,3,4,5,6,7,8,9}','abcdefghij');

    EXEC SQL INSERT INTO test(f,i,a,text) VALUES(140787.0,2,:a,:text);

    EXEC SQL COMMIT;
}

If you can't get it to work with a dynamic array, you can always turn it into a string of the format '{1,2,3,4,5}' and insert that (see the example).