[postgis-users] Setting multiple columns in one UPDATE request

[Alexander Gataric]

Rewrite to have a CTE with the area calculations and join to the table.

⁣Get BlueMail for Android ​

On Sep 27, 2020, 2:29 AM, at 2:29 AM, Marco Boeringa <marco at boeringa.demon.nl> wrote:
>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
>
>_______________________________________________
>postgis-users mailing list
>postgis-users at lists.osgeo.org
>https://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200927/21427eda/attachment.html>