Actually, I had a larger script that did exactly what you propose. However I
started to think that a profecient where clause would do the trick.
In my mapping table, a and b are primary keys. a_id, b_id, c_sort_order.
a_id is the parent and b_id is the child for my purposes, so if a_id is
deleted then all relations are deleted, but if b_id is deleted, then there
stands a chance for an index order in c_sort_order appearing.
Rather then selecting and looping, I thought I could short circut the
procedure by saying
update mapping set c_sort_order = c_sort_order - 1 where a_id = OLD.a_id and
c_sort_order > OLD.c_sort_order.
My thought was that there was no real reason to select and loop as this
function would perform the resort for this series of a_id mappings. It seems
to me that your code does the exact same thing, only in a longer form. Also
there is no need to do anyone less then sort_order since sort_order will be
0 to n-1 where n is the total number of mappings.
a_id, b_id, c_sort_order
1, 1, 0
1, 2, 1
1, 3, 2
1, 4, 3
if you delete where b_id = 1 then you want to update where b_id = 2, 3 and 4
since a_id = 1 and c_sort_order is greater then 0.
Again, the issue was that postgres only executes one delete.
After changing the trigger to an after delete, I was able to delete all and
even delete multiple rows. I now have one small problem that I will have to
test more on. Using my where statement, if i delete from table where b_id =
2 or b_id = 3, c_sort_order becomes out of sync. I will do another test and
see if the select loop fairs any better.
I have a real-world function like so:
CREATE OR REPLACE FUNCTION cms.resort_content_flash() RETURNS TRIGGER AS '
DECLARE
eachrow RECORD;
innerrow RECORD;
sort INT := 0;
BEGIN
FOR eachrow IN EXECUTE ''SELECT * FROM cms.content_flash WHERE flash_id =
'' || OLD.flash_id LOOP
sort := 0;
FOR innerrow IN EXECUTE ''SELECT * FROM cms.content_flash WHERE
content_id = '' || eachrow.content_id || '' ORDER BY sort_order'' LOOP
IF innerrow.flash_id != OLD.flash_id THEN
EXECUTE ''UPDATE cms.content_flash SET sort_order = '' || sort ||
'' WHERE content_id = '' || innerrow.content_id || '' AND flash_id = '' ||
innerrow.flash_id || '''';
sort := sort +1;
END IF;
END LOOP;
END LOOP;
RETURN OLD;
END;
' language 'plpgsql';
that I will rejigger to the test table and try out.
Thanks for the input.
From: Tom Lane <[EMAIL PROTECTED]>
To: Stephan Szabo <[EMAIL PROTECTED]>
CC: Russell Simpkins <[EMAIL PROTECTED]>,
pgsql-sql@postgresql.org
Subject: Re: [SQL] after delete trigger behavior Date: Wed, 22 Jun 2005
15:46:41 -0400
Stephan Szabo <[EMAIL PROTECTED]> writes:
> Is there anything we have right now that will handle this kind of thing
> without requiring either updating all the counts after a deletion in a
> statement trigger or once per row updating all the counts for records
with
> the same "a" (doing something like make a sequence and using it in a
> subselect matching keys)?
The best thing I can think of is your first idea, ie, renumbering all
the rows in a statement-level AFTER DELETE trigger. Something like
(untested)
DECLARE
rec record;
n integer := 1;
BEGIN
FOR rec IN
SELECT * FROM table
WHERE <<grouping cols = rec's grouping cols>>
ORDER BY sort_order
LOOP
IF rec.sort_order != n THEN
UPDATE table SET sort_order = n
WHERE <<primary key = rec's primary key>>;
END IF;
n := n + 1;
END LOOP;
END;
Ugly as this is, it's at least linear in the number of rows to be
changed; the originally proposed trigger was O(N^2) in the number of
rows affected, and would surely be intolerably slow for multiple deletes
in a reasonably sized table. Given an index on the grouping columns
plus sort_order, it could even be reasonably fast (don't forget to make
the ORDER BY match the index).
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
