From: acurtis416 <Annette_Curtis@URMC.Rochester.edu>
To: pgsql-sql@postgresql.org
Subject: Help with Function in plpgsql
Date: Fri, 17 Dec 2010 08:58:54 -0800 (PST)
Message-ID: <1292605134370-3309695.post@n5.nabble.com> (raw)
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 table
retval integer;
imp_rec imperson%ROWTYPE; -- import data feed
person_rec person%ROWTYPE; -- primary database table userid PK - person -
FK to other supporting tables
addrs_rec address%ROWTYPE; -- address table for adding/udpdating
information based on person record results
stud_rec student%ROWTYPE; -- student table for adding/updating information
based on person record results
intdeg 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
tables
FOR 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.visadate as 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
UPDATE student
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
WHERE userid = retval;
ELSIF intdeg = 'PHD' THEN
UPDATE student
SET advisor_phd_admit = imp_rec.advur1
WHERE userid = retval;
ELSIF intdeg = Null THEN
UPDATE student
SET advisor_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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: Annette_Curtis@URMC.Rochester.edu
Subject: Re: Help with Function in plpgsql
In-Reply-To: <1292605134370-3309695.post@n5.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox