plpgsql question

[Jennifer Lee]

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*grandpar ent4)
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 ma**@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).

[Joe Conway]

Jennifer Lee wrote:

something like

Parent1*Parent2
or
(grandparent1*grandparent2)*(grandparent3*grandpar ent4)
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.

You haven't been very specific, so it's hard to offer concrete advice.
But in any case, take a look at split_part() and replace() functions
(and possibly others) here:

http://www.postgresql.org/docs/view....ns-string.html

HTH,

Joe
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster