PostgreSQL PL/Python Extension - Function Not Returning Expected Result

Question

I am facing an issue with a PostgreSQL extension using plpython3u. I have created a simple extension with a Python function that is supposed to add two numbers. However, when I execute the function, it returns an empty result instead of the expected sum.

Here's the Python function in the SQL script file (pg_py_ext--1.0.sql):

-- pg_py_ext--1.0.sql
-- Create the function that uses the Python script
CREATE OR REPLACE FUNCTION add_numbers(a integer, b integer)
RETURNS integer
LANGUAGE plpython3u
AS $$
def add_numbers(a, b):
    return a + b
$$;

I have already verified the Python code, and it appears to be correct. However, the function does not return the expected result (e.g., SELECT add_numbers(3, 5) should return 8, but it returns an empty result).

Output:

spartacus=# SELECT add_numbers(3, 5);
 add_numbers
-------------

(1 row)

I have also checked the PostgreSQL logs for any error messages, but there are no relevant entries.

PostgreSQL version: PostgreSQL 15.0

I'm not sure what else could be causing this issue. Any guidance or suggestions on how to troubleshoot and resolve this problem would be greatly appreciated.

Thank you in advance for your help!

You are returning from the Python function `def add_numbers()` not the Postgres function `add_numbers()`. Simplest solution would not use `def add_numbers()` and just do `return a + b`. Otherwise you will can call the function and use the returned value as the `return` from the Postgres function. — Adrian Klaver, Jul 25 '23 at 18:10
@AdrianKlaver, I just want to show the most basic use of plpython. Funtion is the way to go. I am making a quick start for Postgres extensions so i just want to cover all the bases (this completes the procedural languages) — Ishaan Adarsh, Jul 25 '23 at 18:15
@IshaanAdarsh If you really need a helper function inside `add_numbers` for whatever reason - but especially when used for teaching - you really should name it something different and give it different parameters. Otherwise people might assume that that's the way to actually declare the postgres function. — Bergi, Jul 25 '23 at 19:28

Ishaan Adarsh · Answer 1 · 2023-07-25T19:13:27.177

The issue with the PostgreSQL PL/Python extension not returning the expected result may be due to the incorrect return statement in the Python function.

SQL script file (pg_py_ext--1.0.sql):

-- pg_py_ext--1.0.sql

-- Enable the PL/Python3U language if not already enabled
CREATE EXTENSION IF NOT EXISTS plpython3u;

-- Create the function that uses the Python script
CREATE OR REPLACE FUNCTION add_numbers(a integer, b integer)
RETURNS integer
LANGUAGE plpython3u
AS $$
def add_numbers(a, b):
    return a + b
return add_numbers(int(a), int(b))
$$;

In the updated version, after performing the addition operation in the Python function, we explicitly return the result back to the PostgreSQL function using the return statement. This should resolve the issue and return the correct result.

You could simplify to `return add_numbers(a, b)`. – Adrian Klaver Jul 25 '23 at 18:16 — Adrian Klaver, Jul 25 '23 at 18:16

PostgreSQL PL/Python Extension - Function Not Returning Expected Result

1 Answers1