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

This is a followup to a thread last Friday.  I'll restate the problem
and already-tried solutions, including Tom Lane's from Friday, then I'll
ask for more ideas. :-)

Requirement: I need a plpgsql function that can return to me the list of
all child id's given a parent id.  This will query a table 'entities'
with 'id' and 'parent_id' fields.  Therefore, the nature of the data is
such that this function will need to be recursive.  Additionally, this
function needs to be multiuser-safe in case two connections call it at
the same time with different parent id's.

Limitations/Assumptions (correct me if I am wrong):

1. plpgsql functions cannot return setof's.  So, if I want the set of
child ids, I need to wrap this plpgsql function with an sql function,
which can return setof.

2. The only way to pass the result set from the plpgsql to the sql
function is storing it externally to the function- i.e. in a (temporary
or permanent) table.

3.  I can create a table called descendants that has one column, id.  If
it's a permanent table, I'll have to lock it so that a concurrent call
to the function cannot intersperse id's in the result set.  If it's a
temporary table, it will only be visible to it's own connection anyhow.

4.  If I take the permanent table route, I run into a transaction
problem.  I cannot have the wrapper sql function lock the table, because
the last statement has to be a select that returns the setof integers,
not the commit to unlock the table.  If I put the commit before the
'select descendants.id from descendants', it's not multiuser-safe.  If I
omit the commit, the table stays locked for the rest of the life of the
connection (apprently sql functions do not automatically end
transactions/release locks like plpgsql functions do).  Similarly, if I
lock the table inside the inner plpgsql function, it gets unlocked
before the wrapper sql function returns the result set (because every
plpgsql function is run inside a transaction and the table gets released
as soon as the plpgsql function returns.)  So, it appears there's no
safe way to do it via permanent table.  This was one of Tom's
suggestions, but since a plpgsql function cannot return setof, and I
need to rely on a wrapper sql function for that, I'm not sure how that
could work.

5.  If I go with a temporary table, there is the 'when do i create it'
issue.  I cannot create it in the sql function because i need
conditional logic to say "if it doesnt already exist for this
connection, create it".  If I try to create it when it already exists, I
of course get an error.  On the other hand, I don't know of a way to
check for temporary table existence from within plpgsql in order to know
whether to create it or not.  If that is possible, I might have a
working solution there.  The plpgsql function would check for temp table
existence, create it if not, delete previous contents, and populate it.
  The wrapper would select from the temp table for the return set.
Multiuser safe since it's a temp table.  But not at all sure if it's
even possible.

At this point, I believe my only solution to this problem is to convert
the recursive data in the table into Joe Celko tree style (which would
reduce the problem to "select id from entities where left > (select left
from entities where id = $parentid) and right < (select right from
entities where id = $parentid).  However, if anyone can think of a way
to do this with the current table, this would be preferable for the
short and medium-term.  I appreciate all the help I've received so far,
hopefully we can get this solved before too long.

Thanks,
Fran