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

[Marco Boeringa]

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