I am writing a plpgsql (PostgreSQL 8.x) trigger function that should do
something on a number of records. The records are in a very simple table
with two columns - 'parent_id' and 'child_id'. A 'child' can be as well
a 'parent' to one or more children - in this case its ID appears as many
times in the 'parent_id' column as many children it has. The input the
plpgsql function gets is the ID of the top 'parent'. Then it should find
all children and do something with them.
I think I can do this in two ways -
a) load all record IDs in an array and than loop on it
b) do something like a WHILE cycle in which I select the records one by
one (using increasing offset) - and do the job within this WHILE cycle
The a) way seems easier to me but it might eat too much memory on large
trees (expected tree size is up to let's say 3 000 - 15 000 records).
The b) way seems the best way - I am only not sure if SELECT with
growing offset will work in this case.
I would be happy about some comments.
Thank you,
Iv
--