Re: storing intermediate results in recursive plpgsql f

Hi!

is there possibility to use combination of both, nested sets
and normal way with parent etc.

--
bye,
Uros mailto:uros(dot)gruber(at)sir-mag(dot)com

Friday, May 3, 2002, 10:53:17 PM, you wrote:

AD> I would also be interested to know if there is any way to determine in
AD> plpgsql if temp table already exists...

AD> -----Original Message-----
AD> From: Fran Fabrizio [mailto:ffabrizio(at)mmrd(dot)com]
AD> Sent: Monday, March 04, 2002 12:40 PM
AD> To: pgsql-general(at)postgresql(dot)org
AD> Subject: Re: [GENERAL] storing intermediate results in recursive plpgsql
AD> functions

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

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

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

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

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

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

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

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

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

AD> Thanks,
AD> Fran

AD> ---------------------------(end of broadcast)---------------------------
AD> TIP 4: Don't 'kill -9' the postmaster

AD> ---------------------------(end of broadcast)---------------------------
AD> TIP 6: Have you searched our list archives?

AD> http://archives.postgresql.org