Re: pl/pgsql recursion/arrays (fwd)

On Tue, 4 Sep 2001, Bo Lorentsen wrote:

> Jason Tan wrote:
>
> > I can tell you right now, it works.
>
> That's nice. Are you using the SETOF to make a list (then how do you append to
> it) or did you do something else to accumulate the array ?

No I am not what I did was used a different approach to the problem
because I wanted to solve it faster than I could find the relevant info.

I suspect what you would need to do is pass the array to the fucntion as a
parameter and at the top level call you would pass in an empty array.

I have included a description of the problem I wanted to solve and the
solution below, if you are interested in how I got around the problem
without retunring an array.

Regards
Jason

>
> > I have implemented a function that recurses through records that index on
> > each other in tree style.
>
> Funny thing, this is exactly the problem Im addressing to :-)
>
> > I think I have read all the latest offical doco, and dont recall seeing
> > that (SETOF) there.
>
> Hmm, I don't even remember from where I found this info.
>
> /BL
>


For general interest I will explain the probelm I wanted to solve and show
you my tables and function and the query I use.

To me the problem lends itself quite well to recurison.

The Problem
===========

The problem was I have a database to track people and orgnisations,
amongst other thigns.
People can belong to organisaitons.
They are teid together with a table, belongs_to.

Organisaitons can have sub organisations which are just other
organisations.
Each orgniasation has unique ID.
Each organisaiton has a parent_organisaiton which is either 0, for a
organisaiton that has no parent organisaion or the id of the parent
organisaion.

In this way a Company might exist say Acme Widgets, with a Support
Department which is further broken down into prodyuct support departments,
so teh Acme Widget Support Departmetn might have a Big Widget Support Team
and a Little Widget Support Team. In this model ACME Widgets, ACMEW
Widgets Support Dept, Big Widget Support Team and Little Widget Support
Team would all be organsiaitons. ACME WIdget would be a top level
organisaiton and the other orgs wiould be children(or grandchildren) of
ACME Widget.

I wanted to find all of the people who belonged to an organisaiton and all
of its child organisaitons.

For a  variety of reasons, I did not want to use a nested loop and two
cursors to solve this problem in my application, so I wantedd to keep the
problem solution  in the database or the SQL.

The Schema
==========

The relevant tables were:

contacts=# \d organisation
               Table "organisation"
   Attribute   |     Type     |      Modifier
---------------+--------------+--------------------
 parent_org    | integer      | not null
 org_id        | integer      | not null
 org_type      | integer      | not null
 org_name      | varchar(256) | not null
 short_name    | varchar(256) |
 last_modified | timestamp    | not null
 realm         | integer      | not null default 0
Indices: organisation_org_name_key,
         organisation_pkey

contacts=# \d person
                  Table "person"
   Attribute   |    Type     |      Modifier
---------------+-------------+--------------------
 person_id     | integer     | not null
 person_type   | integer     | not null
 name          | varchar(64) | not null
 surname       | varchar(64) | not null
 middle_name   | varchar(64) |
 last_modified | timestamp   | not null
 realm         | integer     | not null default 0
Index: person_pkey

contacts=# \d belongs_to
            Table "belongs_to"
 Attribute |  Type   |      Modifier
-----------+---------+--------------------
 person_id | integer | not null
 org_id    | integer | not null
 realm     | integer | not null default 0
Index: belongs_to_pkey


The Query
=========
The query I use is:

select person_id, name, surname from person where person_id in
    (select belongs_to_org(person_id,$org_id) from person )
order by surname;


The Function
============
The recursive PL/pgSQL function is definded as:
drop function belongs_to_org(int,int);


create function belongs_to_org(int,int) returns int as'
declare
        result record;
        child   record;
        retval person.person_id%type;
begin

        --first off see if person_id belongs to org_id with asimpel query

        select into result * from belongs_to where person_id=$1 and
org_id=$2;

        if found then
                return $1;
        end if;

        --if we did not return above then the person is not directly
associated
        --with org_id, however they may be assocaited with a child
organisation
        --of org, so we now try and find that out

        for child in select * from organisation where parent_org = $2 loop

                select into retval belongs_to_org($1,child.org_id);
                if( retval != null) then
                        return retval;
                end if;

        end loop;

        --last ditch if we got to heare then we dont match at all return
NULL
        return NULL ;
end;
'language 'plpgsql';




--
------------------------------------------------------------------------------
Jason Tan                                                jason@rebel.net.au
     "Democracy is two wolves and a lamb voting on what to have for lunch.
                 Liberty is a well-armed lamb contesting the vote."
                               ~Benjamin Franklin, 1759
------------------------------------------------------------------------------