Bulk data insertion
Hello,
I have a PL/PgSQL function that I need to call with some ARRAY
parameters. These array values are very large -- typically thousands of
elements. Each element is a 4-element array. This function is called
to do some sanity checking on the array data and use the individual
elements to do inserts where appropriate.
The problem is that I don't want to spend a lot of time and memory
building such a query (in C). I would like to know if there is a way to
take this huge chunk of data and get it into the database in a less
memory-intensive way. I suppose I could use COPY to put the data into a
table with triggers that would do the checks on the data, but it seems
inelegant and I'd like to know if there's a better way.
Thoughts? Thanks for your time.
--
Jonathan Daugherty
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Replication & Support Services, (503) 667-4564
Jonathan Daugherty <jdaugherty@commandprompt.com> writes:
The problem is that I don't want to spend a lot of time and memory
building such a query (in C). I would like to know if there is a way to
take this huge chunk of data and get it into the database in a less
memory-intensive way. I suppose I could use COPY to put the data into a
table with triggers that would do the checks on the data, but it seems
inelegant and I'd like to know if there's a better way.
Actually I'd say that is the elegant way. SQL is fundamentally a
set-oriented (table-oriented) language, and forcing it to do things in
an array fashion is just misusing the tool.
regards, tom lane