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?)