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