Thread: plpgsql handling a set of values

I am writing a plpgsql (PostgreSQL 8.x) trigger function that should do
something on a number of records. The records are in a very simple table
with two columns - 'parent_id' and 'child_id'. A 'child' can be as well
a 'parent' to one or more children - in this case its ID appears as many
times in the 'parent_id' column as many children it has. The input the
plpgsql function gets is the ID of the top 'parent'. Then it should find
all children and do something with them.

I think I can do this in two ways -

a) load all record IDs in an array and than loop on it

b) do something like a WHILE cycle in which I select the records one by
one (using increasing offset) - and do the job within this WHILE cycle

The a) way seems easier to me but it might eat too much memory on large
trees (expected tree size is up to let's say 3 000 - 15 000 records).
The b) way seems the best way - I am only not sure if SELECT with
growing offset will work in this case.

I would be happy about some comments.

Thank you,
Iv

--

On 10/9/06, pobox@verysmall.org <pobox@verysmall.org> wrote:
> I am writing a plpgsql (PostgreSQL 8.x) trigger function that should do
> something on a number of records. The records are in a very simple table
> with two columns - 'parent_id' and 'child_id'. A 'child' can be as well
> a 'parent' to one or more children - in this case its ID appears as many
> times in the 'parent_id' column as many children it has. The input the
> plpgsql function gets is the ID of the top 'parent'. Then it should find
> all children and do something with them.

it's not exactly clear if you are asking about handling children
recursively or not (meaning, you have to find the children's children,
and so on).  If so, I'd suggest taking a look here:
http://people.planetpostgresql.org/merlin/index.php?/archives/2-Dealing-With-Recursive-Sets-With-PLPGSQL.html
which details one way of dealing with recursion in pl/pgsql.

if not, then you are dealing with a simple loop over a set.  more than
likely, this can be handled elegantly with a single query (usually the
best way).  then again, a standard loop might fit better and in this
case a for loop is often easiet:

for foovar in select * from foo
loop
  something := foo.bar;  --etc
end loop;

in this case the postgresql backend is pretty smart and this approach
can work with large sets -- although a single query will often be the
most efficient method.  ymmv

merlin

Merlin Moncure wrote:
 > it's not exactly clear if you are asking about handling children
 > recursively or not (meaning, you have to find the children's children,
 > and so on).  If so, I'd suggest taking a look here:
 >
http://people.planetpostgresql.org/merlin/index.php?/archives/2-Dealing-With-Recursive-Sets-With-PLPGSQL.html


Yes, this is exactly my case.

Thanks a lot!
Iv

pobox@verysmall.org wrote:
> I am writing a plpgsql (PostgreSQL 8.x) trigger function that should do
> something on a number of records. The records are in a very simple table
> with two columns - 'parent_id' and 'child_id'. A 'child' can be as well
> a 'parent' to one or more children - in this case its ID appears as many
> times in the 'parent_id' column as many children it has. The input the
> plpgsql function gets is the ID of the top 'parent'. Then it should find
> all children and do something with them.

I think you should have a look at the ltree contrib package. It
implements tree-like structures, and probably makes your problem much
simpler.

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
    7500 AK Enschede

// Integrate Your World //

> I think you should have a look at the ltree contrib package. It
> implements tree-like structures, and probably makes your problem much
> simpler.

I have never used contrib modules with PostgreSQL - are they easy to
handle - I mean, especially upon upgrade, etc.

On 10/11/06, pobox@verysmall.org <pobox@verysmall.org> wrote:
> > I think you should have a look at the ltree contrib package. It
> > implements tree-like structures, and probably makes your problem much
> > simpler.
>
> I have never used contrib modules with PostgreSQL - are they easy to
> handle - I mean, especially upon upgrade, etc.

installation:
make && make install

upgrade issue is tricky.  their is a lower burden of backwards
compatibility than on in-core features.  for example, some of contrib
was gpl and is getting moved out/changed.  however, the more popular
modules, like tsearch are very well supported.  I haven't used ltree
module myself but on the surface it has some advantages and
disadvantages over the approach I suggested.  However, the authors,
namely Teodor Sigaev and Oleg Bartunov are some pretty amazing coders.

merlin

> installation:
> make && make install
>
> upgrade issue is tricky.  their is a lower burden of backwards
> compatibility than on in-core features.  for example, some of contrib
> was gpl and is getting moved out/changed.  however, the more popular
> modules, like tsearch are very well supported.  I haven't used ltree
> module myself but on the surface it has some advantages and
> disadvantages over the approach I suggested.  However, the authors,
> namely Teodor Sigaev and Oleg Bartunov are some pretty amazing coders.
>
> merlin

Thank you for the information.

I think your article on the BoM helped me solve the issue so far. I am
not that experienced and I prefer to stay on a stable ground as long as
I am not forced to move :)

Thanks again!
Iv