Re: Denormalized field

On Sun, Aug 18, 2013 at 5:56 AM, Robert James <srobertjames(at)gmail(dot)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