Обсуждение: Calling Python functions with parameters
I am having a problem with the simplest of Python functions, so I must be doing something wrong. After hours of searching and trying many options, I need the key that my puny brain is missing here.
I cannot pass parameters to a plpythonu function. I have tried within psql and with pgAdmin III (which adds IN or INOUT to the parameter list - which I can't find documented). I'm an advanced Python programmer but a beginning PostgreSQL user.
Here is what I have, which I copied almost verbatim from example code:
----------------------------
test_dev-# \p
create or replace function testf5i(a integer,b integer)
RETURNS integer AS $$
if a > b:
return a
return b
$$ language plpythonu
test_dev-# \g
CREATE FUNCTION
test_dev=# select testf5i(1,2);
ERROR: plpython: function "testf5i" failed
DETAIL: exceptions.NameError: global name 'a' is not defined
If I remove the parameters and replace the a and b variables with numbers, it works fine.
Any clues for me would be much appreciated!
I'm using PostgreSQL 8.1.10 on Windows (for dev) and 8.1.2 in production in Linux.
--Puzzled in Portland
PS. What I need to do, which I also could find not examples on the mailing lists or the Internet, is to de-normalize some tables (user, addresses, phones, emails) into one big view and then update the proper tables upon updates. The web application then can just get one row and not have to deal with all the different tables
I have this working in theory by using a rule for the user's fields and another rule for when a fax number changes. The problem being I would need too many rules to be easily editable (I would prefer one or two source files so I can search-replace, and put in version control.) I would need five rules for each field (ON UPDATE .. UPDATE if the values change, ON UPDATE .. INSERT if a value were blank and now exist, ON UPDATE .. DELETE if the value was set and is not blank, ON INSERT, and ON DELETE). There are four sets of fields (address, fax, phone, email), and 5 copies (email1, email2, email3, email4, email5).
I REALLY don't want to maintain 80 rules!
So I thought I would just create a function and call it with the built ins OLD and NEW and program a few simple loops. But I cannot pass parameters correctly to the function.
(And I'm surprised that I could find no examples of anyone already doing this? Is there something inherently wrong with this approach?)
I cannot pass parameters to a plpythonu function. I have tried within psql and with pgAdmin III (which adds IN or INOUT to the parameter list - which I can't find documented). I'm an advanced Python programmer but a beginning PostgreSQL user.
Here is what I have, which I copied almost verbatim from example code:
----------------------------
test_dev-# \p
create or replace function testf5i(a integer,b integer)
RETURNS integer AS $$
if a > b:
return a
return b
$$ language plpythonu
test_dev-# \g
CREATE FUNCTION
test_dev=# select testf5i(1,2);
ERROR: plpython: function "testf5i" failed
DETAIL: exceptions.NameError: global name 'a' is not defined
If I remove the parameters and replace the a and b variables with numbers, it works fine.
Any clues for me would be much appreciated!
I'm using PostgreSQL 8.1.10 on Windows (for dev) and 8.1.2 in production in Linux.
--Puzzled in Portland
PS. What I need to do, which I also could find not examples on the mailing lists or the Internet, is to de-normalize some tables (user, addresses, phones, emails) into one big view and then update the proper tables upon updates. The web application then can just get one row and not have to deal with all the different tables
I have this working in theory by using a rule for the user's fields and another rule for when a fax number changes. The problem being I would need too many rules to be easily editable (I would prefer one or two source files so I can search-replace, and put in version control.) I would need five rules for each field (ON UPDATE .. UPDATE if the values change, ON UPDATE .. INSERT if a value were blank and now exist, ON UPDATE .. DELETE if the value was set and is not blank, ON INSERT, and ON DELETE). There are four sets of fields (address, fax, phone, email), and 5 copies (email1, email2, email3, email4, email5).
I REALLY don't want to maintain 80 rules!
So I thought I would just create a function and call it with the built ins OLD and NEW and program a few simple loops. But I cannot pass parameters correctly to the function.
(And I'm surprised that I could find no examples of anyone already doing this? Is there something inherently wrong with this approach?)
Jerry McRae wrote: > I am having a problem with the simplest of Python functions, so I must be > doing something wrong. After hours of searching and trying many options, I > need the key that my puny brain is missing here. > > I cannot pass parameters to a plpythonu function. I have tried within psql > and with pgAdmin III (which adds IN or INOUT to the parameter list - which I > can't find documented). I'm an advanced Python programmer but a beginning > PostgreSQL user. > > Here is what I have, which I copied almost verbatim from example code: > ---------------------------- > test_dev-# \p > create or replace function testf5i(a integer,b integer) > RETURNS integer AS $$ > if a > b: > return a > return b > $$ language plpythonu > > > test_dev-# \g > CREATE FUNCTION > test_dev=# select testf5i(1,2); > ERROR: plpython: function "testf5i" failed > DETAIL: exceptions.NameError: global name 'a' is not defined That looks like the same error I received in my first attempts at plpython. After muddling with it for some time (and this was my first practical Python code at all, so you can imagine how confused I was), I added (the equivalent to your code) at the beginning: a = a b = b As ridiculous as that looks, it's what worked for me. Although, function contained a class with several methods (the function creates "slugs" on the fly from proper names for use in URLs, eg. Élisabeth Carrière -> elisabeth_carriere). So, maybe my own problems were related to that. PlPython remains a mystery to me, in many respects. It's certainly *not* the best way to learn Python. > PS. What I need to do, which I also could find not examples on the mailing > lists or the Internet, is to de-normalize some tables (user, addresses, > phones, emails) into one big view and then update the proper tables upon > updates. The web application then can just get one row and not have to deal > with all the different tables > > ... > > (And I'm surprised that I could find no examples of anyone already doing > this? Is there something inherently wrong with this approach?) Perhaps it's simply not worthwhile. That seems like a great deal of work just to avoid having to deal with several tables. (I didn't end up using my slug-creator function, myself, and moved that logic into the application scripts.) b