Re: [GENERAL] Denormalized field
On Mon, Aug 19, 2013 at 4:27 AM, Vik Fearing wrote: > Yes, I would use a trigger for this. > > > This is definitely the right answer, but keep in mind that this will slow down your inserts since it calls slow_function for each insert. Make sure you can afford that performance hit.
Re: [GENERAL] Denormalized field
On Sun, Aug 18, 2013 at 5:56 AM, Robert James 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Denormalized field
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
