Lists: | pgsql-general |
---|
From: | Robert James <srobertjames(at)gmail(dot)com> |
---|---|
To: | Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Denormalized field |
Date: | 2013-08-18 03:56:46 |
Message-ID: | CAGYyBghm+4yOWMeMLkWeQvD7NLMVsFFKW2YKcqmJM=JRMYCkuA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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?
From: | Vik Fearing <vik(dot)fearing(at)dalibo(dot)com> |
---|---|
To: | Robert James <srobertjames(at)gmail(dot)com> |
Cc: | Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Denormalized field |
Date: | 2013-08-19 08:27:09 |
Message-ID: | 5211D6DD.2030403@dalibo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Luca Ferrari <fluca1978(at)infinito(dot)it> |
---|---|
To: | Robert James <srobertjames(at)gmail(dot)com> |
Cc: | Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Denormalized field |
Date: | 2013-08-19 08:28:19 |
Message-ID: | CAKoxK+5mhHy6v_L3u+A5mwA+AfQYSThDjeRq2-nDiqaA07gG9Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | BladeOfLight16 <bladeoflight16(at)gmail(dot)com> |
---|---|
To: | Robert James <srobertjames(at)gmail(dot)com> |
Cc: | Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Denormalized field |
Date: | 2013-08-20 03:41:38 |
Message-ID: | CA+=1U=VcpD07A_9eyEsFJhUsaDrNxfkB2afXT6BpEujdcV_yZg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Mon, Aug 19, 2013 at 4:27 AM, Vik Fearing <vik(dot)fearing(at)dalibo(dot)com> wrote:
> Yes, I would use a trigger for this.
>
> <snip>
>
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.