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

[Marco Boeringa]

Thanks for pointing that out Alexander. I was just about to start a test 
after some code modifications, but now realized based on the example you 
pointed out that I was missing the "FROM cte" clause in my SQL 
statement. It feels a bit unnatural to have to specify that one, as you 
already define the cte name after the WITH keyword. But this example 
sorted it out.

Curious to see how it runs and if it leads to a measurable performance 
enhancement.

Marco

Op 27-9-2020 om 18:30 schreef 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 <http://www.bluemail.me/r?b=16117>
> On Sep 27, 2020, at 7:47 AM, Marco Boeringa <marco at boeringa.demon.nl 
> <mailto: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
>
>
> _______________________________________________
> 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/2ef7864d/attachment.html>