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