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@comcast.net