Re: BUG #3811: Getting multiple values from a sequence generator

Hi, <br /><div class="gmail_quote"><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204);
margin:0pt 0pt 0pt 0.8ex; padding-left: 1ex;">> Right, I want to use it with a bulk operation, say importing a
millionrecords <br />> with COPY. Calling nextval one million times looks to me like an enormous waste<br />> of
resources.Suppose, you are on an ADSL line: it will cost one million times<br />> the ping time of the ADSL line
(say10 milliseconds per call). Well OK, one <br />> could write a server function that does this, but then the one
millionresult<br />> values must be transported back to the client, because they are not guaranteed<br />> to be
contiguous.Unneeded complexity compared to a simple nextval increment <br />> parameter.<br /><br />The usual way to
usenextval() is to use it on the server as an expression in<br />an INSERT or DEFAULT. If you're using COPY and don't
havea column default set<br />up then, hm, I guess you're kind of stuck. That would make a good use case for <br />a
one-timenextval(increment) or something like that.<br /><div class="Ih2E3d"></div></blockquote></div><br
/>Coincidently,I very briefly discussed (offline) about supporting expressions while doing loads using COPY FROM with
Heikkia while back. From the above mail exchanges, it does appear that adding this kind of functionality will be useful
whiledoing bulk imports into tables using COPY. <br /><br />Heikki's initial suggestion was as follows:<br /><br />COPY
<table>FROM <file> USING <query> <br /><br />Where query could be any SELECT query, executed once for
rowusing the values from the input data file. For example: <br /><br />COPY footable (strcol, strcollen, moredata) FROM
<file>USING SELECT $1, length($1), $2;<br clear="all" /><br />The sql expressions could refer to the columns
beingread or could be user defined procedures, built-in functions etc too. These expressions would need to be executed
perrow read from the input data file to form a new set of values[], nulls[] entries before forming the corresponding
tupleentry. <br /><br />I think the above will be a very useful enhancement to COPY. The syntax and other details
mentionedabove are ofcourse subject to discussion and approval on the list.<br /><br />Regards,<br />Nikhils<br />--
<br/>EnterpriseDB               <a href="http://www.enterprisedb.com">http://www.enterprisedb.com</a>