Postgres Gurus: Insert a List as bulk?

[ADJob]

Hi,

We are about to write a routine that transfer the data from native datafile to Postgresql database. I have managed to do this, but it takes an eternity. And now I am searching a faster way to transfer all data and I have found three ways to do this:

1. Loop within an object class from a prepared list (todays solution)
-----------------------------------------------------------------------
Do lv_DATA.$definefromsqlclass('myschema')
Do lv_DATA.$sessionobject.$assign(pgsql)

Set current list pv_LIST (prepared list)
If pv_LIST.$linecount>0
   For each line in list from 1 to #LN step 1
       Do lv_DATA.$assignrow(pv_LIST)
       Do lv_DATA.$insert() Returns #F
   End For
End If


2. Using pv_LIST.$doinserts
-----------------------------------------------------------------------
Have not tested this yet, but I do not think it will be much faster. You will only move the same loop from the Object Class to the Table Class.


3. Using COPY (= moving the loop to the Postgresql)
-----------------------------------------------------------------------
A useful technique within PostgreSQL is to use the COPY command to insert values directly into tables from external files. But it will sure be a roundabout way to create a file?

Begin statement
Sta: COPY mytable
Sta: FROM 'c:/dir1/file1.txt'
End statement

I am searching a way to use a list without having to create a file. Or a way to create the file in a fast way. Like the statement of my dreams:

Begin statement
Sta: COPY mytable
Sta: FROM pv_LIST
End statement

Any clue, advice, solution or example appreciated.

Thanks in advance and greetings from a sunny Sweden,

Mats Olsson, ADJob Nordic AB