Postgres Pro
Downloads
Home   >   mailing lists

Thread: plpgsql question

plpgsql question

From
"Jennifer Lee"
Date:

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

Re: plpgsql question

From
Joe Conway
Date:
Jennifer Lee wrote:
> 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.

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.php?version=7.3&idoc=0&file=functions-string.html

HTH,

Joe