title: How to efficiently convert the price in SELECT using the existing exchange rate table with given structure?
body: <p><strong>Situation:</strong> I have the <strong>exchange rate table</strong> like this:</p>
  
  <pre><code>date_from   cur1 coef1          cur2 coef2
  2017-01-01  CZK  27.000000000   EUR  1.000000000
  2017-07-03  EUR  1.000000000    CZK  26.150000000
  2017-07-03  JPY  100.000000000  CZK  19.500000000
  2017-10-05  JPY  1000.0000000   EUR  7.54761885
  </code></pre>
  
  <p>Notice that sometimes the <code>cur1</code> and <code>cur2</code> can be switched for the same pair. The table contains also other currency pairs. The reason for the two coefficients is that the table is filled manually (to get the numbers more comprehensible by a human brain -- see the JPY conversion).</p>
  
  <p>Then I have another <strong>table with invoice rows</strong> where the price is expressed in the local currency (that is, <strong>each row has it own currency unit</strong> near the price value).</p>
  
  <p><strong>I need</strong> to do some <code>SELECT</code> over the invoice-row table and transform <strong>the price</strong> to be shown <strong>in the chosen target currency</strong> (say, <em>everything in Euro</em>). How to do that efficiently?</p>
  
  <p><strong>My first attempts:</strong> I know the target currency in advance. It means it should probably be better to build a temporary table with simplified structure to be joined easily.  Let the target currency be EUR. Then only subset of the above table will be used, some pairs be switched, and the two coefficients be converted to one rate. The target currency will be fixed or implicit. From the above table, the JPY-CZK pair would not be part of the table:</p>
  
  <pre><code>date_from   cur  rate
  2017-01-01  CZK  27.000000000
  2017-07-03  CZK  26.150000000
  2017-10-05  JPY  0.00754761885
  </code></pre>
  
  <p>To join the rows with another table I need not only the <code>date_from</code> but also <code>date_to</code>. To be able to use <code>BETWEEN</code> in the join condition, I would like to have the <code>date_to</code> as the one just before the next period. Here for CZK, I need to have a record like:</p>
  
  <pre><code>date_from   date_to     cur  rate
  2017-01-01  2017-07-02  CZK  27.000000000
  </code></pre>
  
  <p>Notice the one day off in the <code>date_to</code> from the next <code>date_from</code>.</p>
  
  <p>However, I need to add automatically also some boundary values for the dates before and after the explicitly expressed intervals. I need something like that:</p>
  
  <pre><code>date_from   date_to     cur  rate
  1900-01-01  2016-12-31  CZK  27.000000000   &lt;-- guessed rate from the next; fixed start at the year 1900
  2017-01-01  2017-07-02  CZK  27.000000000
  2017-07-03  3000-01-01  CZK  26.150000000   &lt;-- explicit rate; boundary year to 3000
  </code></pre>
  
  <p>Plus similarly for the other currencies in the same temporary table...</p>
  
  <pre><code>1900-01-01  2017-10-04  JPY  0.00754761885  &lt;-- rate guessed from the next; fictional date_from
  2017-10-05  3000-01-01  JPY  0.00754761885  &lt;-- explicit rate; fictional date_to
  </code></pre>
  
  <p>How can I efficiently construct such temporary table?</p>
  
  <p>Do you have any other suggestions related to the problem?</p>
  
  <p><strong>Update:</strong> I have posted my solution to Code Review <a href="https://codereview.stackexchange.com/q/177517/16189">https://codereview.stackexchange.com/q/177517/16189</a> Please, have a look to find the flaws.</p>