PL/Python ERROR: NameError: global name 'name of user-defined function' is not defined

Question

I am running Postgres 9.3 and I am trying to create a PL/Python function calling a user-defined sql function but I get the below error message after calling the function using SELECT * FROM f2(TEXT1, TEXT2):

ERROR: NameError: global name 'f1' is not defined
SQL state: XX000
Context: Traceback (most recent call last):
PL/Python function "f2", line 1, in <module>
print f1() 
PL/Python function "f2"

The sql function looks like this:

CREATE FUNCTION f1(TEXT, TEXT) RETURNS SETOF some_tbl AS $$
 SELECT col1, col2, col3
 FROM some_other_tbl
 WHERE col1=$1 AND col2=$2
 GROUP BY col1;
$$ LANGUAGE 'sql';

And the PL/Python function like this:

CREATE FUNCTION f2(TEXT, TEXT) RETURNS SETOF some_tbl
  AS $$ return f1() $$ 
  LANGUAGE 'plpython2u';

So, it seems the f1 is not found within f2, even though I can call it on its own.

Any pointers on this would be appreciated? Thanks

Answer 1

It doesn't work that way. An SQL function is not directly callable as a Python function. (It might be a neat feature, but nothing of the sort is implemented.) If you want to call SQL functions from PL/Python, you need to do

plpy.execute("SELECT f1(...)")

etc.