Please help me understand this rule. I created it but I am wondering if some of the criteria in the WHERE clause is even necessary, particularly "setdata.seriesid=new.seri
esid". I think I need to understand "NEW" better. It seems to be that setdata.seriesid will always equal new.seriesid, right?
Here is what I'm trying to do. I want to update a record and set SETDATA.OTHERIDS after or while a row is being inserted. I get this OTHERIDS value from the get_otherids() function, which queries a table called SERIESDATA.
I have tested everything and it seems to be correct but I've only tested on a very small dataset. I will end up using this on tables that have over 40,000 rows so I want to make sure the rule only acts on the row in question so it is very fast. The last thing I want is for a user to sit around for 10 minutes for each row they insert.
One more thing. You may wonder why not do the updating at the application level at the same time the row is initially being inserted. I would like to avoid rolling out a new version of the application with changes. I figured it would be fine to just create a rule or trigger (though I've read that a rule is better in my case) so that this change is transparent to the user. I'm not sure what best practice would be for using rules and triggers.
id | otherids | group
2256798 | 888 | groupQ
1234567 | 789 | groupR
7654321 | 562 |
2943747 | 511 | groupR
2944517 | 123 | groupR
insert into setdata(seriesid,name,group) values(1234567,'John','groupR');
insert into setdata(seriesid,name,group) values(7654321,'Sue','groupR');
seriesid | name | otherids | group
1234567 | John | 789 | groupR
7654321 | Sue | 562 | groupQ
Here is the rule:
CREATE OR REPLACE RULE upd_setdata_on_insert AS
ON INSERT TO setdata DO UPDATE setdata SET otherids = get_otherids(new.id)
WHERE (setdata.group::text = 'groupR'::text OR setdata.group::text = 'groupQ'::text) AND setdata.seriesid = new.seriesid AND setdata.otherids IS NULL;
Here it the function:
CREATE OR REPLACE FUNCTION get_otherids(integer)
RETURNS character varying AS '
serid alias for $1;
select into other otherids from seriesdata where seriesdata.id=serid;
' LANGUAGE 'plpgsql' VOLATILE;