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