Solved

PL/Python NameError: global name '<variable>' is not defined

Posted on 2013-12-17
7
1,459 Views
Last Modified: 2013-12-23
I'm trying to create a PL/Python function for PostgreSQL.  Here's a simplified version that gets the error.
create or replace function eztest( ASIN varchar(10)) returns  integer AS
$$
plan = plpy.prepare("Select bookid from book where ASIN = $1", ["text"])
rv = plpy.execute(plan, [ ASIN ])
return rv["bookid"]
$$ language plpython2u;

Select eztest('B00DMYO2D2');

Open in new window


When I call the function with the SELECT statement, it complains
ERROR:  NameError: global name 'ASIN' is not defined
CONTEXT:  Traceback (most recent call last):
  PL/Python function "eztest", line 3, in <module>
    rv = plpy.execute(plan, [ ASIN ])
PL/Python function "eztest"

Open in new window


If I hardcode a value in, rather than using my variable, it works.  What am I doing wrong?

Thanks!
0
Comment
Question by:Daniel Wilson
  • 4
  • 2
7 Comments
 
LVL 28

Assisted Solution

by:pepr
pepr earned 250 total points
ID: 39725995
I do not know plpy, and I cannot check it. Anyway, it seems you have to quote the ASIN  in the call. It should be a string literal, not a variable. Something like
rv = plpy.execute(plan, [ "ASIN" ])

Open in new window

0
 
LVL 9

Assisted Solution

by:techtonik
techtonik earned 250 total points
ID: 39727640
I can't find any reference that types like VARCHAR(10) are allowed in Python/PL.

Try to replace function parameter with eztest(ASIN text)
0
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 0 total points
ID: 39727897
Changing to text gets the same result.

However, changing the parameter name to lowercase resolved the issue.  Must have been a requirement I overlooked that parameters be lowercase.

create or replace function eztest( asin varchar(10)) returns  integer AS
$$
plan = plpy.prepare("Select bookid from book where ASIN = $1", ["text"])
rv = plpy.execute(plan, [ asin ])
return rv[0]["bookid"]
$$ language plpython2u;

Select eztest('B00DMYO2D2');

Open in new window

0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 9

Expert Comment

by:techtonik
ID: 39729026
Cool. Is this requirement specified somewhere?
0
 
LVL 32

Author Comment

by:Daniel Wilson
ID: 39729066
I haven't found a spec that says this.  So my results should be considered anecdotal and applying to my particular Ubuntu 12.04 LTS installation running PostgreSQL 9.3 and running under VMWare Player.  But ... perhaps ... the same solution will work in other contexts.
0
 
LVL 32

Author Comment

by:Daniel Wilson
ID: 39729075
As for mapping Postgres data types to Python, that is specified:
http://www.postgresql.org/docs/9.3/static/plpython-data.html#AEN60813

Any of the character types maps to a Python str.
0
 
LVL 32

Author Closing Comment

by:Daniel Wilson
ID: 39735718
Thanks for the ideas!
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Less strange, but still introduction This introduction was added (1st August, 2011) to reflect some reactions.  Firstly, the term basics in the title of the article...  As any other word, it is a symbol with meaning attached to the word by some a…
The purpose of this article is to demonstrate how we can use conditional statements using Python.
Learn the basics of lists in Python. Lists, as their name suggests, are a means for ordering and storing values. : Lists are declared using brackets; for example: t = [1, 2, 3]: Lists may contain a mix of data types; for example: t = ['string', 1, T…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now