0

I would like something like this:

UPDATE tab1
   SET name = descr = proc1(name)
 WHERE id &lt 1000

unlike double calling:

UPDATE tab1
   SET name = proc1(name),
       descr = proc1(name)
 WHERE id &lt 1000

Is that possible?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
user694989
  • 75
  • 6
  • [Very similar question just yesterday.](http://stackoverflow.com/questions/21863163/reuse-computed-select-value) – Erwin Brandstetter Feb 19 '14 at 18:04
  • Cool answer. Unfortunatelly CTE is not applicable to UPDATE. – user694989 Feb 20 '14 at 12:39
  • But it is (since Postgres 9.1). Have a look at [the manual here.](http://www.postgresql.org/docs/current/interactive/queries-with.html#QUERIES-WITH-MODIFYING) [Related answer with complex example.](http://stackoverflow.com/questions/19413254/optimize-insert-update-delete-operation/19439591#19439591) – Erwin Brandstetter Feb 20 '14 at 17:02
  • Yes you right I use 8.4. Alas. May be next year we will upgrade to 9.x. May be. – user694989 Feb 21 '14 at 12:19

1 Answers1

0

Not as you write. I assume that proc1 is expensive/time consuming to invoke? If so, you may be able to use a subquery, at the cost of doing an additional join:

UPDATE tab1
SET name = p.p1n
    descr = p.p1n
FROM (
    SELECT id, proc1(name)
    FROM tab1
) p(id, p1n)
WHERE tab1.id = p.id
AND   tab1.id < 1000;
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778