how to make an SQL UPDATE from record returning function

From: Rafal Pietrak <rafal(at)zorro(dot)isa-geek(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: how to make an SQL UPDATE from record returning function
Date: 2012-04-24 07:02:56
Message-ID: 1335250976.27563.49.camel@localhost.localdomain
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all,

Recently I have fell onto a multicolumn update problem, earlier
discussed here:

http://postgresql.1045698.n5.nabble.com/UPDATE-of-several-columns-using-SELECT-statement-td1916045.html

But in my case, subselect does not help, since in my case, new values
for a row I get from an output of record returning function ... and real
problem is that this function is quite expensive to run.

I currently check this on pg v8.4, and it doesn't work just like in that
2009. I was wondering if the 9th release changes anything, or may be
there is a workaround?

I actually try to:

UPDATE my_table SET (col1,col2) = my_function(col3, col4, ...);

And running the function twice:

UPDATE my_table SET col1 = my_func1(col3, col4, ...), col2 =
my_func2(col3, col4, ...);

is not an option, since the function is *very* expensive (multiple join
of large tables - inventories, history, etc).

Is there a syntax workaround that I could possibly use to get the effect
of launching my_function just once?

-R

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Abel Abraham Camarillo Ojeda 2012-04-24 07:48:59 Re: how to make an SQL UPDATE from record returning function
Previous Message Chris Angelico 2012-04-24 01:43:21 Re: Chaining inserts ... This would be cool