Re: how to make an SQL UPDATE from record returning function

On Tue, Apr 24, 2012 at 2:02 AM, Rafal Pietrak <rafal(at)zorro(dot)isa-geek(dot)com> wrote:
> 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
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Why don't create table my_table which stores the composite value by itself (not
in two parts)?

create type myfunctype (col1 datatype, col2 datatype);

create table my_table (mydata myfunctype);

update my_table set mydata = my_func1(col3, col4, ...);

or use a temporary table which uses the composite type and "sync"
somehow to my_table splitting it...

it's just an idea... I suppose there are better ways...