?
Solved

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

Posted on 2013-12-17
7
Medium Priority
?
1,861 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 29

Assisted Solution

by:pepr
pepr earned 1000 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 1000 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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Here I am using Python IDLE(GUI) to write a simple program and save it, so that we can just execute it in future. Because when we write any program and exit from Python then program that we have written will be lost. So for not losing our program we…
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.

762 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