Thread: plpy prepare problem
I'm having a bit of a problem getting plpython's prepare to work properly: CREATE OR REPLACE FUNCTION batch_item_reversal(b batch_item) RETURNS "varchar" AS $BODY$ if b['reversal_flag'] == 'Y': sql = plpy.prepare(""" SELECT batch_item_number FROM batch_item WHERE patient_ssn=$1 AND patient_dob=$1 AND claim_number=$1 AND batch_item_number != $1""", ["varchar", "date", "varchar", "varchar"]) refs = plpy.execute(sql, [ b['patient_ssn'], b['patient_dob'], b['claim_number'], b['batch_item_number']]) refs2 = plpy.execute(""" SELECT batch_item_number FROM batch_item WHERE patient_ssn='%s' AND patient_dob='%s' AND claim_number='%s' AND batch_item_number != '%s' """ % (b['patient_ssn'], b['patient_dob'], b['claim_number'], b['batch_item_number'])) if refs: return refs[0]["batch_item_number"] else: return "ERROR" else: return None $BODY$ LANGUAGE 'plpythonu' VOLATILE; Here, refs2 returns the proper data, but refs always returns nothing. I have a feeling it has something to do with the type list, I tried all "text"'s but to no avail.
On Sunday 01 April 2007 9:09 am, jlowery wrote: > I'm having a bit of a problem getting plpython's prepare to work > properly: > > CREATE OR REPLACE FUNCTION batch_item_reversal(b batch_item) > RETURNS "varchar" AS > $BODY$ > > if b['reversal_flag'] == 'Y': > sql = plpy.prepare(""" > SELECT batch_item_number > FROM batch_item > WHERE patient_ssn=$1 AND > patient_dob=$1 AND > claim_number=$1 AND > batch_item_number != $1""", > ["varchar", "date", "varchar", "varchar"]) > refs = plpy.execute(sql, [ > b['patient_ssn'], > b['patient_dob'], > b['claim_number'], > b['batch_item_number']]) You need to have unique numbers for the variables. patient_ssn=$1 patient_dob=$2 etc > > refs2 = plpy.execute(""" > SELECT batch_item_number > FROM batch_item > WHERE patient_ssn='%s' AND > patient_dob='%s' AND > claim_number='%s' AND > batch_item_number != '%s' > """ % (b['patient_ssn'], > b['patient_dob'], > b['claim_number'], > b['batch_item_number'])) > > if refs: > return refs[0]["batch_item_number"] > else: > return "ERROR" > else: > return None > > $BODY$ > LANGUAGE 'plpythonu' VOLATILE; > > > Here, refs2 returns the proper data, but refs always returns nothing. > I have a feeling it has something to do with the type list, I tried > all "text"'s but to no avail. > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org/ -- Adrian Klaver aklaver@comcast.net