plpy prepare problem

From: "jlowery" <jslowery(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: plpy prepare problem
Date: 2007-04-01 16:09:30
Message-ID: 1175443770.465127.269920@l77g2000hsb.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Rohit Prakash Khare 2007-04-01 17:28:13 Problem restoring from backup file
Previous Message Ron Mayer 2007-04-01 10:44:26 Re: Arrays instead of join tables