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
*****************************************************************************************
DISCLAIMER:
This email is from the Scottish Crop Research Institute, but the views expressed by the sender are not necessarily the views of SCRI and its subsidiaries. This email and any files transmitted with it are confidential to the intended recipient at the e-mail address to which it has been addressed. It may not be disclosed or used by any other than that addressee.
If you are not the intended recipient you are requested to preserve this confidentiality and you must not use, disclose, copy, print or rely on this e-mail in any way. Please notify mail@scri.sari.ac.uk quoting the name of the sender and delete the email from your system.
Although SCRI has taken reasonable precautions to ensure no viruses are present in this email, neither the Institute nor the sender accepts any responsibility for any viruses, and it is your responsibility to scan the email and the attachments (if any).
