Re: plpgsql handling a set of values

On 10/9/06, pobox(at)verysmall(dot)org <pobox(at)verysmall(dot)org> 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