Postgresのstored functionで配列を引数にし，Pythonから呼び出す

投稿者: utsuboka 投稿日: 2024-08-19 in Postgresql、python

How to call a Postgres stored function with an array as an argument from Python

あまりサンプルがなかったので，簡単なものを作成してみた。

Postgres 16
Python 12

配列で数字のリストを渡して，ストアドファンクションで合計を計算し，返す

Postgresql


drop function if exists testfunc;
create function testfunc(ary integer[])
returns integer as
$$
declare tmp integer;
begin
select sum(s) into tmp from unnest(ary) as s;
return tmp;
end;
$$
language plpgsql;

select testfunc(ARRAY[1,2,3]);

Python

import psycopg2

try:
    ps_connection = psycopg2.connect(user="username",
                                     password="password",
                                     host="127.0.0.1",
                                     port="5432",
                                     database="testdatabase")

    cursor = ps_connection.cursor()

    lst = [1,2,3]
    # call stored procedure
    cursor.execute('select testfunc(%s)', (lst,))

    result = cursor.fetchall()
    for row in result:
        print("sum = ", row[0], )

except (Exception, psycopg2.DatabaseError) as error:
    print("Error while connecting to PostgreSQL", error)

finally:
    # closing database connection.
    if ps_connection:
        cursor.close()
        ps_connection.close()
        print("PostgreSQL connection is closed")

Postgresql