Solved

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

Posted on 2013-12-17
7
1,759 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
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

Upcoming Webinar: Securing your MySQL/MariaDB data

Join Percona’s Chief Evangelist, Colin Charles as he presents Securing your MySQL®/MariaDB® data on Tuesday, July 11, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7).

Question has a verified solution.

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

Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
Sequence is something that used to store data in it in very simple words. Let us just create a list first. To create a list first of all we need to give a name to our list which I have taken as “COURSE” followed by equals sign and finally enclosed …
Learn the basics of strings in Python: declaration, operations, indices, and slicing. Strings are declared with quotations; for example: s = "string": Strings are immutable.: Strings may be concatenated or multiplied using the addition and multiplic…
Learn the basics of modules and packages in Python. Every Python file is a module, ending in the suffix: .py: Modules are a collection of functions and variables.: Packages are a collection of modules.: Module functions and variables are accessed us…

728 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