<html><head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<meta http-equiv="content-type" content="text/html; charset=UTF-8"></head><body style="zoom: 0%;"><div dir="auto">You have the correct link. Here's an example.<br><br></div>
<div dir="auto"><a href="https://stackoverflow.com/questions/36908495/update-with-result-from-cte-postgresql">https://stackoverflow.com/questions/36908495/update-with-result-from-cte-postgresql</a><br><br></div>
<div dir="auto"><!-- tmjah_g_1299s -->Get <!-- tmjah_g_1299e --><a href="http://www.bluemail.me/r?b=16117"><!-- 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 7:47 AM, Marco Boeringa <<a href="mailto:marco@boeringa.demon.nl" target="_blank">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;">
<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">
<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>
<pre class="blue"><hr><br>postgis-users mailing list<br>postgis-users@lists.osgeo.org<br><a href="https://lists.osgeo.org/mailman/listinfo/postgis-users">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></pre></blockquote></div></body></html>