Help with Function in plpgsql - Mailing list pgsql-sql

Hello Postgres Team

My environment is 8.4.5 I use PGADMIN 1.10. I've written a function in which
I'm having difficulty debugging to determine whether I have a logic error or
what I'm attempting to do is not possible in plpgsql? Understand that I come
from the Windows and Microsoft World. I'm use to VB and VBA recordsets. 

Here is the code I wrote. My issue is that I'm not sure I've moved the for
loop properly to insert a new record from my files. I keep getting a
duplicate key violation. Any help would be appreciated to understanding if
this is a logic error or not possible in plpgsql. I am in the process of
trying to learn python. Our current platform is python 2.6.

Thank you for your assistance.

CREATE OR REPLACE FUNCTION update_info() RETURNS SETOF imperson AS
$BODY$
DECLARE-- define record for person table, define record for ISIS feed, define
record for address tableretval integer;imp_rec imperson%ROWTYPE;    -- import data feedperson_rec person%ROWTYPE;    --
primarydatabase table userid PK - person -
 
FK to other supporting tablesaddrs_rec address%ROWTYPE;    -- address table for adding/udpdating
information based on person record resultsstud_rec student%ROWTYPE;    -- student table for adding/updating
information
based on person record resultsintdeg text;            -- variable for intended degree to check which advisor level
to update on new student inserted

BEGIN-- start by creating a record for each row of the import table from the
ISIS feed-- Loop through each record of import feed until matched id found in main
person table-- then update existing record in person table use return value of userid
to update address table too-- if import record not matched then insert into person, address, student
tablesFOR imp_rec IN SELECT * FROM imperson LOOP    SELECT INTO person_rec *     FROM person    WHERE person_rec.univid
=imp_rec.uid;    IF EXISTS (SELECT person_rec.univid FROM imperson WHERE
 
person_rec.univid=imp_rec.uid) THEN        UPDATE person        SET fname = imp_rec.fn, lname = imp_rec.ln, mname =
imp_rec.mn,dob =
 
imp_rec.dob, gender = imp_rec.gender, race = imp_rec.race, ethnicity =
imp_rec.ethnicity, i9_verified = imp_rec.i9, visa = imp_rec.visatype,
visa_exp_date = imp_rec.visadate, confidential_flag = imp_rec.confflag        WHERE person_rec.univid = imp_rec.uid;
   SELECT INTO addrs_rec *         FROM address        WHERE addrs_rec.userid = person_rec.userid;            UPDATE
address       SET cur_addr_street = imp_rec.schladr1 || ' ' || imp_rec.schladr2,
 
cur_addr_city = imp_rec.schlcity, cur_addr_state = imp_rec.schlst,
cur_addr_zip = imp_rec.schlzip, perm_addr_street = imp_rec.oschladr1 || ' '
|| imp_rec.oschladr2, perm_addr_city = imp_rec.oschlcity, perm_addr_state =
imp_rec.oschlst, perm_addr_zip = imp_rec.oschlzip, bill_addr_street =
imp_rec.badr1 || ' ' || imp_rec.badr2, bill_addr_city = imp_rec.bcity,
bill_addr_state = imp_rec.bst, bill_addr_zip = imp_rec.bzip,
emer_contact_name = imp_rec.emergname, emer_contact_rel = imp_rec.emergrel,
emer_contact_hphone = imp_rec.emergph, tel_home = imp_rec.schlph, home_email
= imp_rec.email        WHERE addrs_rec.userid = person_rec.userid;    ELSE        INSERT INTO person (userid, fname,
lname,mname, dob, gender, race,
 
ethnicity, univid, i9_verified, visa, confidential_flag) VALUES
(nextval('per_userid_seq'), imp_rec.fn, imp_rec.ln, imp_rec.mn,
CAST(imp_rec.dob as DATE), imp_rec.gender, imp_rec.race, imp_rec.ethnicity,
imp_rec.uid, imp_rec.i9, imp_rec.visatype, imp_rec.confflag) RETURNING
userid INTO retval;        IF  imp_rec.visadate IS NULL THEN            UPDATE person            SET visa_exp_date =
null           WHERE userid = retval;        ELSE            UPDATE person            SET visa_exp_date =
CAST(imp_rec.visadateas DATE)                    WHERE userid = retval;        END IF;        INSERT INTO address
(addrid,userid,cur_addr_street,cur_addr_city,
 
cur_addr_state, cur_addr_zip, perm_addr_street, perm_addr_city,
perm_addr_state, perm_addr_zip, bill_addr_street, bill_addr_city,
bill_addr_state, bill_addr_zip, emer_contact_name, emer_contact_rel,
emer_contact_hphone, tel_home, home_email) VALUES
(nextval('addrs_addrid_seq'), retval, imp_rec.schladr1 || ' ' ||
imp_rec.schladr2, imp_rec.schlcity, imp_rec.schlst, imp_rec.schlzip,
imp_rec.oschladr1 || ' ' || imp_rec.oschladr2, imp_rec.oschlcity,
imp_rec.oschlst, imp_rec.oschlzip, imp_rec.badr1 || ' ' || imp_rec.badr2,
imp_rec.bcity, imp_rec.bst, imp_rec.bzip, imp_rec.emergname,
imp_rec.emergrel, imp_rec.emergph, imp_rec.schlph, imp_rec.email);        INSERT INTO student (studentid, userid,
studentstatus)VALUES
 
(nextval('stud_studentid_seq'), retval, 'C');        intdeg := imp_rec.intdeg;        IF intdeg = 'MS' THEN
UPDATEstudent            SET advisor_g_admit = imp_rec.advur1            WHERE userid = retval;        ELSIF intdeg =
'BS'THEN            UPDATE student            SET advisor_ug_admit = imp_rec.advur1            WHERE userid = retval;
    ELSIF intdeg = 'DNP' THEN            UPDATE student            SET advisor_phd_admit = imp_rec.advur1
WHEREuserid = retval;        ELSIF intdeg = 'PHD' THEN            UPDATE student            SET advisor_phd_admit =
imp_rec.advur1           WHERE userid = retval;        ELSIF intdeg = Null THEN            UPDATE student
SETadvisor_phd_admit = null, advisor_g_admit = null, advisor_ug_admit =
 
null            WHERE userid = retval;        END IF;    END IF;RETURN NEXT imp_rec;END LOOP;
END;
$BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100 ROWS 2000;
ALTER FUNCTION update_info() OWNER TO acurtis;
GRANT EXECUTE ON FUNCTION update_info() TO public;
GRANT EXECUTE ON FUNCTION update_info() TO acurtis;
-- 
View this message in context:
http://postgresql.1045698.n5.nabble.com/Help-with-Function-in-plpgsql-tp3309695p3309695.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.