Thread: Re: [BUGS] 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> 

"NikhilS" <nikkhils@gmail.com> writes:

> Coincidently, I very briefly discussed (offline) about supporting
> expressions while doing loads using COPY FROM with Heikki a while back. From
> the above mail exchanges, it does appear that adding this kind of
> functionality will be useful while doing bulk imports into tables using
> COPY.
>
> Heikki's initial suggestion was as follows:
>
> COPY <table> FROM <file> USING <query>
>
> Where query could be any SELECT query, executed once for row using the
> values from the input data file. For example:

Another direction to head would be to take away COPY's special logic to insert
into tables and instead have something like:

COPY FROM <file> USING <query>

where <query> is an *INSERT* statement. Or for that matter a DELETE or an
UPDATE. It would prepare the query then execute it once per line read from the
streamed copy data.

It would be much more general but perhaps be harder to optimize the our
current COPY can be optimized.

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support!