I've the next UPDATE statement:

update mytable
   set a = first_part(genid()),
       b = second_part(genid()),
       c = third_path(genid())
 where package_id = 10;

Within this example the function genid() is known as three occasions for every row, that is wrong - I would like it to be known as only one time for every row of mytable.

I am using PostgreSQL 8.4 database. Crafting the right update?

I have attempted something similar to this:

update mytable
   set a = first_part(g),
       b = second_part(g),
       c = third_path(g)
 where package_id = 10
  from genid() as g;

However it did not work, because genid() continues to be known as only once for the entire update statement.

Perhaps you have attempted Postgres' non-standard UPDATE .. FROM clause? I imagine, this could work

update mytable
   set a = first_part(gen.id),
       b = second_part(gen.id),
       c = third_path(gen.id)
  from (
          select genid() as genid, id
          from mytable 
          where package_id = 10
       ) gen
 where mytable.id = gen.id;
 --and package_id = 10 -- This predicate is no longer necessary as the subquery
                       -- already filters on package_id, as Erwin mentioned

Observe that I am forcing genid() to become known as exactly once per record in mytable inside the subselect. Then I am self-joining mytable and gen utilizing a hypothetical id column. Begin to see the documentation here:


This appears to possess been introduced with Postgres 9. only, though. In the event that appears too complicated (i.e. not so readable), you are able to still turn to pgplsql as user Florin suggested here.

Are you able to check if this sounds like employed in postgres?

     a, b, c, genid() as val
  from mytable
  where package_id = 10
set a = first_part(val),
       b = second_part(val),
       c = third_path(val);

Update: only for the ideea: Are you able to check it out having a cursor?

         a, b, c, genid() as val
      from mytable
      where package_id = 10; 

I recommend to make use of following -

DECLARE @genID VARCHAR(100) -- or appropriate datatype

SET @genID = genid() -- Or select @genID = genid()    
update mytable    
   set a = first_part(@genID),
       b = second_part(@genID),
       c = third_path(@genID)
where package_id = 10;