Calling Python functions with parameters

From: "user" <2567jl03(at)sneakemail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Calling Python functions with parameters
Date: 2008-07-18 20:45:14
Message-ID: 769-02320@sneakemail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Browse pgsql-general by date

  From Date Subject
Next Message Francisco Reyes 2008-07-18 20:48:26 Re: Reducing memory usage of insert into select operations?
Previous Message Raymond O'Donnell 2008-07-18 20:38:05 Re: Writing a user defined function