CODEWITHSUNDEEP
pythonarrayspostgresqlstored-procedurespg8000
Javide
Javide

Reputation: 2657

How to pass NULLs in array to Postgres stored procedure using pg8000?

In Python 3.8 I'm using pg8000 1.21.3 to call a Postgress 11 stored procedure which takes as an argument a custom data type array. Here is the working proof of concept schema and stored procedure:

DROP PROCEDURE IF EXISTS myproc;
DROP TABLE IF EXISTS mytable;
DROP TYPE mytype;

CREATE TYPE mytype AS (
    an_int     INT,
    a_bool     BOOL,
    a_decimal  DECIMAL,
    a_string   TEXT
);

CREATE TABLE mytable(
    id         SERIAL PRIMARY KEY,
    an_int     INT,
    a_bool     BOOL,
    a_decimal  DECIMAL,
    a_string   TEXT
);

CREATE PROCEDURE myproc(
    IN myarray mytype[]
)
AS $$
BEGIN
        INSERT INTO mytable(
            an_int,
            a_bool,
            a_decimal,
            a_string
        ) SELECT
            an_int,
            a_bool,
            a_decimal,
            a_string
        FROM
            unnest(myarray);
    END;
$$
LANGUAGE 'plpgsql';

CALL myproc(
    array[
            (1, 1, 2.1, 'foo'),
            (NULL, NULL, NULL, NULL)
        ]::mytype[]
);

SELECT * FROM mytable;

The output of the select is:

 id | an_int | a_bool | a_decimal | a_string 
----+--------+--------+-----------+----------
  1 |      1 | t      |       2.1 | foo
  2 |        |        |           | 
(2 rows)

meaning that when I invoke the stored procedure from SQL, I can create a row with all null values.

I am now trying to achieve the same from Python using pg8000. To pass an array with some null values, the only way it seems to work is to replace None with an empty string. This works for integers and text data types, but it fails for booleans and decimals:

import pg8000

arr = [(1, True, 3.2, 'foo'),
       (None, True, 3.2, 'foo'),
       (1, True, 3.2, None),
       (1, False, 3.2, 'foo'),
       (1, None, 3.2, 'foo'),  # <---- boolean null fails
        (1, False, None, 'foo'), # <---- decimal null fails
       ]

sql = """CALL myproc(%s::mytype[]);"""
stm = [str(item) for item in arr]
stm = [item.replace('None', '') for item in stm]

with pg8000.connect(host="localhost", database="mydb", user="myuser", password="mypass") as con:
    with con.cursor() as cursor:
        cursor.execute(
            sql,
            args=(stm,)
        )
    con.commit()

and the error is:

pg8000.dbapi.ProgrammingError: {'S': 'ERROR', 'V': 'ERROR', 'C': '22P02', 'M': 'invalid input syntax for type boolean: " "', 'F': 'bool.c', 'L': '154', 'R': 'boolin'}

How can I pass nulls for all these data types using pg8000 parameter interpolation, that is without hardcoding the sql statement?

Upvotes: 0

Views: 201

Answers (1)

Tony Locke
Tony Locke

Reputation: 454

With version 1.29.6 of pg8000 you should be able to do:

import pg8000

arr = [
    (1, True, 3.2, "foo"),
    (None, True, 3.2, "foo"),
    (1, True, 3.2, None),
    (1, False, 3.2, "foo"),
    (1, None, 3.2, "foo"),  # <---- boolean null fails
    (1, False, None, "foo"),  # <---- decimal null fails
]

sql = """CALL myproc(%s::mytype[]);"""

with pg8000.connect(database="typetest", user="postgres", password="pg") as con:
    with con.cursor() as cursor:
        cursor.execute(sql, args=(arr,))
    con.commit()

since pg8000 now interprets a tuple as a composite type.

Upvotes: 0

Related Questions