plpgsql question

Hi there,

I have a database where I’m given data in an MSAccess table which I then need to move into the appropriate table in my postgresql database (version 7.3.4). There are a couple of fields which I am trying to parse. The most complicated is a plant pedigree field and looks something like

Parent1*Parent2

or

(grandparent1*grandparent2)*(grandparent3*grandparent4)

or something even more complex

The field is called ancest in table passport_temp.

I need to separate each of the parents and/or grandparents etc to load them individually in a pedigree table. I’m trying to do this in plpgsql, but haven’t been able to figure out if there is a way to parse the field.

I’ve started the function like this…

CREATE OR REPLACE FUNCTION parse_ancest() returns integer as '

            DECLARE

                        name record;

                        parent1 text;

                        parent2 text;

            BEGIN

            FOR name IN

                        SELECT DISTINCT ancest FROM passport_temp

                                    WHERE ancest IS NOT NULL

                        LOOP

Then I’m stuck. I’ve not been able to figure out if there a way to select just part of name.ancest and set parent1 to that? I’d like to split the field on either (, ) or *.

Since all my other functions are in plpgsql I wanted to try and do this in it as well before I resort to plperl. Any suggestions? Can anyone recommend a good source for learning plpgsql?

Thanks for any help,

Jennifer