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

I use a function written in the PLTCL for a recursive stored procedure. It
could be written in PLPG but was written for speed in pltcl and i haven't
got round to updating it yet

Here it is though it might prove some use for you

CREATE OR REPLACE FUNCTION sp_go_up_loc_tree(VARCHAR) RETURNS VARCHAR AS '
    if { $1 == "" } {
        return ""
    }
    set inv_loc_id $1
    append return_list "$inv_loc_id "
    spi_exec -array C "SELECT inv_p_loc_id
                       FROM inv_loc_parents
                       WHERE inv_loc_id=$inv_loc_id" {
         spi_exec "SELECT sp_go_up_loc_tree($C(inv_p_loc_id)) AS parent"
         append return_list "$parent "
    }
    if { ![info exists return_list] } {
        return ""
    }
    return $return_list
' LANGUAGE 'pltcl';

This function returns a tcl list from it and you can basically pass back
whater you want.

TCL List has format { item1 } { item 2 } { item3 }

You could even return a tcl list of lists this would allow you to return
more than one thing i.e.

{ { item1 } { name1 } { description1 } } { { item2 } { name2 } {
description2 } }

This may help or it may not if you haven't used the TCL API and would like
mods written feel free to contact me and i will be most happy to write the
recursive function for you

Darren


Darren Ferguson
Software Engineer
Openband

On Thu, 13 Dec 2001, Philip Hallstrom wrote:

> As someone else mentioned Joe Celko's book has some good stuff in it.  You
> might also try this code snippet.  It's currently in PHP and happens
> outside of the database, but you should get a feeling for it.  It works
> pretty well for me.
>
> http://stuff.adhesivemedia.com/php/heirarchial-sorting.php
>
>
>
> -philip
>
> On Thu, 13 Dec 2001, Fran Fabrizio wrote:
>
> >
> > Hello,
> >
> > I've got a plpgsql function that is recursive.  Basically, it traverses
> > a table that represents a tree, which in turn represents parent-child
> > relationships.  So, I have a function, get_descendants.  For each pass,
> > it gets the children of some id.  Then it recurses and looks for the
> > children of all of those children, etc...So, along the way, I'm building
> > a list of ids that represent the whole family.
> >
> > For lack of a better idea, I'm storing the id's into a table on each
> > pass.  So, if I recurse three levels, I'm doing three inserts.  When the
> > recursion exits, I simply select the entire table and then I delete all
> > rows from it.  The performance hit I take is unacceptable, something
> > like .02 - .03 seconds per insert, and it's adding up due to the amount
> > of times I have to run this function.  The end result is that the web
> > page that displays this data takes many seconds to run.
> >
> > Is there some sort of data structure in plpgsql (an array) that I can
> > use instead of the hack of inserting into a table on each pass and
> > selecting back out at the end?  I have to find a way to optimize this
> > process further.
> >
> > Thanks,
> > Fran
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>