Solved

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

Posted on 2013-12-17
7
1,577 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 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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 is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

A set of related code is known to be a Module, it helps us to organize our code logically which is much easier for us to understand and use it. Module is an object with arbitrarily named attributes which can be used in binding and referencing. …
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 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…
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.

809 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