Skip site navigation (1) Skip section navigation (2)

Re: pl/pgsql recursion/arrays (fwd)

From: Jason Tan <jason(at)rebel(dot)rebel(dot)net(dot)au>
To: Bo Lorentsen <bl(at)netgroup(dot)dk>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: pl/pgsql recursion/arrays (fwd)
Date: 2001-09-05 05:23:47
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
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.


> > 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
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

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,

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'
        result record;
        child   record;
        retval person.person_id%type;

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

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

        if found then
                return $1;
        end if;

        --if we did not return above then the person is not directly
        --with org_id, however they may be assocaited with a child
        --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
        return NULL ;
'language 'plpgsql';

Jason Tan                                                jason(at)rebel(dot)net(dot)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

In response to


pgsql-novice by date

Next:From: Oliver ElphickDate: 2001-09-06 06:42:15
Subject: Re: avg() from multiple columns
Previous:From: Jason TanDate: 2001-09-05 03:02:31
Subject: Re: avg() from multiple columns

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group