Brian, that's not what I meant.
Parsing of the uploaded file is just for the purpose of extracting the
components of each spreadsheet row and constructing the INSERTs.
Actually, whenever I copy from a file, either using COPY or with a
custom importer, I put the data into a staging table, so that I can
pre-process before writing to the main table. But why would COPYing from
a file be so insecure?
nextval() and sequences are not what I'm looking for. I want to assign
the same id to all the rows imported from the same file. Let's say user
A is working on portfolio_id 3, and decides to upload a spreadsheet with
new values. I want to be able to import the spreadsheet into the staging
table, and assign a portfolio_id of 3 to all its entries.
Of course, I can't just UPDATE the staging table to have portfolio_id =
3, because user B might also be uploading a sheet for portfolio_id = 9.
Any ideas on this?
Thanks
Jaime
brian wrote:
Jaime Silvela wrote:
I've written a web application where users can upload spreadsheets,
instead of having to key in forms. The spreadsheets get parsed and
INSERTED into a table, and with the INSERT gets added an identifier
so that I can always trace back what a particular row in the table
corresponds to.
I'd like to use COPY - FROM to achieve the same thing, but a stopping
point is that I don't see how to add the new spreadsheet with a
particular identifier.
I'd like to be able to do something like
COPY mytable (field-1, .. field-n, id = my_id) FROM file; or
COPY mytable FROM file WITH id = my_id;
A very messy solution would be to create a temp table with a special
name, COPY to it, then INSERT from it to the permanent table.
However, I don't want a solution of that type.
I may have completely misunderstood you, but i'd think that copying
the data directly from an uploaded file would be more than a little
insecure. But then, you also mentioned that you parse the uploaded
file. I don't understand how these two statements can be compatible.
Do you mean that you'd like to load the data into a table, then
retrieve the sequence ID? Presumably, if your application is really
parsing the data first, one could simply do an INSERT and then grab
the last inserted ID. Look at nextval() & currval().
http://www.postgresql.org/docs/7.3/static/functions-sequence.html
brian
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
***********************************************************************
Bear Stearns is not responsible for any recommendation, solicitation,
offer or agreement or any information about any transaction, customer
account or account activity contained in this communication.
Bear Stearns does not provide tax, legal or accounting advice. You
should consult your own tax, legal and accounting advisors before
engaging in any transaction. In order for Bear Stearns to comply with
Internal Revenue Service Circular 230 (if applicable), you are notified
that any discussion of U.S. federal tax issues contained or referred to
herein is not intended or written to be used, and cannot be used, for
the purpose of: (A) avoiding penalties that may be imposed under the
Internal Revenue Code; nor (B) promoting, marketing or recommending to
another party any transaction or matter addressed herein.
***********************************************************************
