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.