Обсуждение: Denormalized field

I have a slow_function.  My table has field f, and since slow_function
is slow, I need to denormalize and store slow_function(f) as a field.

What's the best way to do this automatically? Can this be done with
triggers? (On UPDATE or INSERT, SET slow_function_f =
slow_function(new_f) ) How?

Will creating an index on slow_function(f) do this?

On 08/18/2013 05:56 AM, Robert James wrote:
> I have a slow_function.  My table has field f, and since slow_function
> is slow, I need to denormalize and store slow_function(f) as a field.
>
> What's the best way to do this automatically? Can this be done with
> triggers? (On UPDATE or INSERT, SET slow_function_f =
> slow_function(new_f) )

Yes, I would use a trigger for this.

> How?

Like so:

alter table t add column slow_function_f datatype;
update t set slow_function_f = slow_function(f);

create function slow_function_trigger()
returns trigger as
$$
begin
    new.slow_function_f = slow_function(new.f);
    return new;
end;
$$
language plpgsql;

create trigger slow_function_trigger
before insert or update of f, slow_function_f on t
for each row
execute procedure slow_function_trigger();


Note: I wrote this directly in my mail client so there might be an error
or two.

> Will creating an index on slow_function(f) do this?

No, creating an index won't do all that for you.  And now you should
just create the index on t.slow_function_f, not on slow_function(t.f).
--
Vik

On Sun, Aug 18, 2013 at 5:56 AM, Robert James <srobertjames@gmail.com> wrote:
> What's the best way to do this automatically? Can this be done with
> triggers? (On UPDATE or INSERT, SET slow_function_f =
> slow_function(new_f) ) How?
>

Define  a before trigger that updates your column. For instance:

CREATE OR REPLACE FUNCTION f_trigger() RETURNS TRIGGER AS $$ BEGIN
NEW.f_field := f_function( NEW.pk ); RETURN NEW; END $$ LANGUAGE
plpgsql;

CREATE TRIGGER tr_foo BEFORE INSERT OR UPDATE ON foo FOR EACH ROW
EXECUTE PROCEDURE f_trigger();

Of course, adjust the trigger and the trigger function to check
against some conditions (e.g., insert, update, nulls).

> Will creating an index on slow_function(f) do this?
>

You can create the index on the function result, assuming it is immutable.

Luca