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

[Marco Boeringa]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200927/8084b8d5/attachment.html>