Re: Recursion in plpgsql

> How can i tell function to call herself like up in PHP
> example. And then is it possible to put data in some kind
> temporary table and when it finish doing recursion i will get
> data from that table and then use it in PHP.

You can just call it like this:

create function myfunc(int4) returns int4 as '
DECLARE
foototal int4;
foo int4;
BEGIN
--do some stuff
--store some data in a table for later retrieval
--if this is the recursive case then do the following
select into foo myfunc(foo);
foototal := foo + foototal;
--etc....
return foo;
END;
' language 'plpgsql';

Something like that, this is off the top of my head.

I did run into trouble when using temporary (as opposed to permanent
ones which I just deleted from every time - a hack, I know) tables. I
can't remember exactly what the specific problem was, but it was some
sort of catch-22 as to where to create the temp table so that the
function could see it and then I could still access it after the
function ran but without having another call to the same function from
the same session cause overwriting issues. I remember it evolved into a
mess of trying to do transactions around the recursive function (you can
go back and read my thread about it on here if you wish) but the end
result was that I converted from a linked list model to a nested set
model and solved the problem that way. Which brings me to my next point...

> Is there anyone who can know how to solve this or maybe have
> better idea or maybe it not worth to worry about this because
> it's fast enough.

If it's fast enough stay with what you've got. Also, Joe Celko's
book 'SQL for Smarties' has an alternate technique called nested sets.
Depending on
which operations you need to do on your data, it may be faster. In
particular, I've
quantitatively observed that for my data at least, nested set approach
is faster for determining all children of a particular parent, or all
parents for a particular child, but that to find the immediate parent,
it's actually quicker to do recursion/linked list style like you've done
rather than nested sets.

Hope that helps,
Fran