Solved

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

Posted on 2013-12-17
7
1,705 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 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Merge lists using index in Python 1 127
PostgreSQL Syntax 12 171
import 13 row from .csv file into another csv file 11 70
How to backup in postgresql 8 50
Introduction On September 29, 2012, the Python 3.3.0 was released; nothing extremely unexpected,  yet another, better version of Python. But, if you work in Microsoft Windows, you should notice that the Python Launcher for Windows was introduced wi…
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…
Learn the basics of while and for loops in Python.  while loops are used for testing while, or until, a condition is met: The structure of a while loop is as follows:     while <condition>:         do something         repeate: The break statement m…

738 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