From: | Darren Ferguson <darren(at)crystalballinc(dot)com> |
---|---|
To: | ffabrizio(at)mmrd(dot)com |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: storing intermediate results from recursive plpgsql |
Date: | 2001-12-14 05:08:21 |
Message-ID: | Pine.LNX.4.10.10112132357540.8180-100000@thread.crystalballinc.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Snow | 2001-12-14 05:26:53 | Re: storing intermediate results from recursive plpgsql |
Previous Message | Stephan Szabo | 2001-12-14 02:02:41 | Re: Correction: Working on "SELECT * WHERE numeric_col = |