[GENERAL] plpy prepare problem

[jlowery]

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.


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org/