plpgsql question - Mailing list pgsql-general

From Jennifer Lee
Subject plpgsql question
Date
Msg-id BE57D4C018CC2642AF256E2FDDA909573693D1@sims.scri.sari.ac.uk
Whole thread Raw
Responses Re: plpgsql question
List pgsql-general

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

pgsql-general by date:

Previous
From: Richard Huxton
Date:
Subject: Re: [Fwd: [LIH]OpenOffice.org 1.1 <-> PostgreSQL connectivity]
Next
From: MaRcElO PeReIrA
Date:
Subject: slow down on UPDATE using IN statements