<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<p>Hi Alexander, <br>
</p>
<p>I guess I could use the optional WITH clause that is part of
UPDATE in PostgreSQL?:</p>
<div class="moz-cite-prefix"><a class="moz-txt-link-freetext" href="https://www.postgresql.org/docs/12/sql-update.html">https://www.postgresql.org/docs/12/sql-update.html</a></div>
<div class="moz-cite-prefix"><br>
</div>
<div class="moz-cite-prefix">It would have been nice to see an
example of such usage in the PostgreSQL help, but I'll figure it
out.</div>
<div class="moz-cite-prefix"><br>
</div>
<div class="moz-cite-prefix">Marco</div>
<div class="moz-cite-prefix"><br>
</div>
<div class="moz-cite-prefix">Op 27-9-2020 om 13:59 schreef Alexander
Gataric:<br>
</div>
<blockquote type="cite"
cite="mid:7548c833-814a-4847-960e-e67d637d86a9@usa.net">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<div dir="auto">Rewrite to have a CTE with the area calculations
and join to the table.<br>
<br>
</div>
<div dir="auto"><!-- tmjah_g_1299s -->Get
<!-- tmjah_g_1299e --><a href="http://www.bluemail.me/r?b=16117"
moz-do-not-send="true"><!-- tmjah_g_1299s -->BlueMail for
Android<!-- tmjah_g_1299e --></a><!-- tmjah_g_1299s -->
<!-- tmjah_g_1299e --></div>
<div class="gmail_quote">On Sep 27, 2020, at 2:29 AM, Marco
Boeringa <<a href="mailto:marco@boeringa.demon.nl"
target="_blank" moz-do-not-send="true">marco@boeringa.demon.nl</a>>
wrote:
<blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt
0.8ex; border-left: 1px solid rgb(204, 204, 204);
padding-left: 1ex;">
<pre class="blue">Hi all,
This may be an irrelevant basic question, but I just cannot find a clear
answer to this, there is no documentation in the PostgreSQL docs for
UPDATE about this:
In case I set multiple columns using some PostGIS function that clearly
has a (considerable) cost associated with it, and the value of the first
column being set is also needed to set the second column, does
PostgreSQL automatically optimize this and re-use the value already
calculated for column 1 to set column 2's value, or is each column's SET
statement treated as independent entity?
E.g. let's say I want to calculate both area, and the division of area
and perimeter, like:
UPDATE <MY_TABLE> SET area = ST_Area(<GEOMETRY_COLUMN>), area_perimeter
= ST_Area(<GEOMETRY_COLUMN>) / ST_Perimeter(<GEOMETRY_COLUMN>)
Will PostgreSQL only process 'ST_Area(<GEOMETRY_COLUMN>)' once in this
case, thus saving CPU load, or do I need to rewrite the SQL statement
somehow to achieve that?
Marco
<hr>
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" moz-do-not-send="true">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></pre>
</blockquote>
</div>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<pre class="moz-quote-pre" wrap="">_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>
<a class="moz-txt-link-freetext" href="https://lists.osgeo.org/mailman/listinfo/postgis-users">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></pre>
</blockquote>
</body>
</html>