Postgresql

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

投稿者: utsuboka 投稿日: in Postgresql、python

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

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

  • Postgres 16
  • Python 12

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

Postgresql


drop function if exists matfunc;
create function matfunc(mat integer[][])
returns integer as
$$
declare 
  res integer := 0;
  i integer;
  j integer;
begin

for i in array_lower(mat,1)..array_upper(mat,1) loop
  for j in array_lower(mat,2)..array_upper(mat,2) loop
    res := res + mat[i][j];
  end loop;
end loop;
return res;
end;
$$
language plpgsql;

select matfunc(ARRAY[ARRAY[1,2,3], ARRAY[4,5,6]]);

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],[4,5,6]]
    # call stored procedure
    cursor.execute('select matfunc(%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")

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

投稿者: utsuboka 投稿日: 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")