On 10/9/06, pobox@xxxxxxxxxxxxx <pobox@xxxxxxxxxxxxx> wrote:
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.
it's not exactly clear if you are asking about handling children recursively or not (meaning, you have to find the children's children, and so on). If so, I'd suggest taking a look here: http://people.planetpostgresql.org/merlin/index.php?/archives/2-Dealing-With-Recursive-Sets-With-PLPGSQL.html which details one way of dealing with recursion in pl/pgsql. if not, then you are dealing with a simple loop over a set. more than likely, this can be handled elegantly with a single query (usually the best way). then again, a standard loop might fit better and in this case a for loop is often easiet: for foovar in select * from foo loop something := foo.bar; --etc end loop; in this case the postgresql backend is pretty smart and this approach can work with large sets -- although a single query will often be the most efficient method. ymmv merlin
