Message-ID: <39425586.2E418BAC@alpha.hut.fi>
Date: Sat, 10 Jun 2000 17:49:42 +0300
From: "Jorma O. =?iso-8859-1?Q?T=E4htinen?=" <jotahtin@alpha.hut.fi>
MIME-Version: 1.0
To: pgsql-interfaces@postgresql.org
Subject: ecpg: how select/insert n rows (array) in one query
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Hello

I'm new postgres user (currenly converting db application with quite
large database from mysql to postgres, hopefully pg is not too slow but
I really need triggers and views).

Anyway could somebody please explain how multiple row select/insert
should be used in embedded sql (ecpg)

1) select - this is simple (well documented)
    EXEC SQL BEGIN DECLARE SECTION;
    struct data_t {
        char md5[100][32];
        int  size[100];
    } data;
    struct ind_t { short md5,size } ind;
    EXEC SQL END DECLARE SECTION;

    EXEC SQL BEGIN TRANSACTION;
    EXEC SQL DECLARE cur CURSOR FOR
        select md5,size FROM filetable;
    EXEC SQL OPEN cur;
    EXEC SQL WHENEVER NOT FOUND DO break;
    while(1) {
        EXEC SQL FETCH 1000 FROM cur INTO :data:ind;
        for(n=0;n<sqlca.sqlerrd[2];++n) { /*do something*/
    }
    EXEC SQL CLOSE cur;
    EXEC SQL COMMIT;

    This is fine, speed on my hardware about (magnitude) 10.000 rows/per
sec.
    (mostly limited by disk i/o)

    Only question here is: why can't I declare that data struct like
    struct data_t {
        char md5[32];
        int size;
    } data[100];
    Is this possible and how to use it.
.
2) insert - Now this is my real problem
    if I have understod docs correctly : use of insert like select in
about example is NOT possible
    please somebody say that I'm wrong - small example would be nice to
see ;-)

    And yes I have tried to insert multiple rows in one transaction
block like
    EXEC SQL BEGIN TRANSACTION
        for(....) {
            insert .....
        }
    EXEC SQL COMMIT

    but this is still way to slow at max 50-100 insert per sec. I
understand that inserting is slow operation
    but 10.000/sec in select and 50/sec in insert - can't be THAT
slow??? (inserting 12-13M rows is a bit pain...;-)

    And yes, I really want to use c+embedded sql code to do inserting
(real app/table struct of course more complex)

    -jt-