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

[Alexander Gataric]

You have the correct link. Here's an example.

https://stackoverflow.com/questions/36908495/update-with-result-from-cte-postgresql

⁣Get BlueMail for Android ​

On Sep 27, 2020, 7:47 AM, at 7:47 AM, Marco Boeringa <marco at boeringa.demon.nl> wrote:
>Hi Alexander,
>
>I guess I could use the optional WITH clause that is part of UPDATE in 
>PostgreSQL?:
>
>https://www.postgresql.org/docs/12/sql-update.html
>
>It would have been nice to see an example of such usage in the 
>PostgreSQL help, but I'll figure it out.
>
>Marco
>
>Op 27-9-2020 om 13:59 schreef Alexander Gataric:
>> Rewrite to have a CTE with the area calculations and join to the
>table.
>>
>> Get BlueMail for Android <http://www.bluemail.me/r?b=16117>
>> On Sep 27, 2020, at 2:29 AM, Marco Boeringa <marco at boeringa.demon.nl 
>> <mailto: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
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
>
>------------------------------------------------------------------------
>
>_______________________________________________
>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/9b171224/attachment.html>