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

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

On Tue, Apr 24, 2012 at 2:02 AM, Rafal Pietrak <rafal@zorro.isa-geek.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@postgresql.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...

On Tue, 2012-04-24 at 02:48 -0500, Abel Abraham Camarillo Ojeda wrote:
[----------------]
>
> Why don't create table my_table which stores the composite value by itself (not
>     in two parts)?

Hmmm. OK. mea coulpa. I didn't follow the SQL good practice, and I don't
have a unique ID column in the updated table. So, I cannot "cook
updates" in temporary store (a table), and then post them against
relevant rows.

The function computes updated values for each row (and is expensive),
and I have the particular row "pinpointed" only within the UPDATE
transaction.

And btw: the updated table is "kind of big", and only selected rows are
updated at a time. The selected subset is a tiny fraction of the whole
table, and only because of fine-tuning that sellection, the update keeps
pace with the growth of the table.

So, as of now, temporary storage in additional table want work for me.
thus I'm still looking for a solution.

-R

Rafal Pietrak, 24.04.2012 09:02:
> 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?

With 9.1 you could probably achieve this using a writeable CTE.

Although I have to admit I don't really understand what your function is returning.
Does the function return a result set or scalar values?

On Tue, 2012-04-24 at 12:10 +0200, Thomas Kellerer wrote:
> Rafal Pietrak, 24.04.2012 09:02:
> >
> > 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?
>
> With 9.1 you could probably achieve this using a writeable CTE.
>
> Although I have to admit I don't really understand what your function is returning.
> Does the function return a result set or scalar values?
>

Basically, it's at shopping cart update.

It ranks updated an item with costommer rating of that item with respect
to other orders and current stock (so that when the cart is only
presented, the values are there, precomputted on update). There is more
then one value to precompute, so the function returns a set of values.
(earlier I only needed to precompute just one value and everything was
so easy then... :).

Could you pls give me an example of how the "writeable CTE"? This does
not ring a bell here.


-R