storing intermediate results in recursive plpgsql functions - Mailing list pgsql-general

Hello,

I have a table 'entity'.  It has an id column and a parent_id column
that refers to another entity in the same table.  I often need to
calculate all of the "descendants" of a particular id.  I wrote a
plpgsql function 'get_descendants' that, given an id, recurses over all
the children of that id, and the children's children, etc... As it
recurses, it puts the ids of the descendants into a one-column table
called 'descendants'.

Since the return of this function needs to be a setof int4, and as best
I can tell there's no way to return a setof from a recursive plpgsql
function (nowhere internally to store the intermediate result set), I
call this function from a wrapper sql function that simply calls the
recursive function, and then selects from the 'descendants' table.

This all works great until two users simultaneously call
get_descendants() wanting the descendants of two different people.  They
of course get mixed and matched in the descendants table.  I thought I
knew of a couple of different ways to possibly solve the problem but I'm
finding out that they don't work so well after all.

First I thought I would just have the function create 'descendants' as a
temporary table instead of a permanent one, but since table names get
converted to OIDs when I load the function into the db, I'm getting
Relation Not Found errors. I'm guessing that trying to refer to
temporary tables from stored procedures is not a common practice. :-)

Then I thought I'd just have the sql function obtain an access exclusive
lock on the 'descendants' table, call the recursive plpgsql function,
select the results from 'descendants', then unlock the 'descendants'
table.  Then the second instance of get_descendants would sit and wait
for descendants to come free.  That doesn't work because the sql
function wants the last statement called to be the one that returns the
setof int4, not the commit; that is necessary.

Right now the only way I can think of to do this is to have a bit flag
that the client can examine to see if anyone else is currently calling
get_descendants and to wait if so and try again to obtain the bit for
itself in a second or so.  I am the first to admit that this would be a
horrendous hack (yeah, like the rest of what I've already done isn't ;)

I feel like I'm missing something obvious.  But it seems the exact
combination I have here (sql function calling a recursive plpgsql
function and then selecting the result set from a table) is just enough
to thwart all the usual techniques.  I have a short mental list of
things I should have done/could do to maybe help:

1. Stop using recursive functions and represent the family tree in Joe
Celko fashion (I learned about his existence after the creation of this
database, unfortunately)

2. Figure out how to store an array of integers as the intermediate
result set on every pass of a recursive plpgsql function and return them
as a setof int4 when I'm done recursing.  I remember trying a while on
this and finding it impossible.

I still feel like I'm missing some obvious solution.  Anyone have any
insight?

Thanks,
Fran