Re: plpy prepare problem

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(at)comcast(dot)net