In Postgres 11 I have a stored procedure which takes as a parameter a nested array. Here is the proof of concept schema and stored proc:
CREATE TYPE child_type AS
(
i INT,
c TEXT
);
CREATE TYPE parent_type AS
(
p TEXT,
children child_type[] -- nested array in stored proc
);
CREATE TABLE grandparent
(
id SERIAL PRIMARY KEY,
g TEXT
);
CREATE TABLE parent
(
id SERIAL PRIMARY KEY,
p TEXT,
grandparent_id INT
);
CREATE TABLE child
(
id INT,
c TEXT,
parent_id INT
);
------------------------------------------------------------
CREATE PROCEDURE insert_family(
IN p_g grandparent.g%TYPE,
IN p_parents_array parent_type[] -- outer array
)
AS $$
DECLARE
v_grandparent_id grandparent.id%TYPE;
v_parent_id parent.id%TYPE;
v_parent parent_type;
v_child child_type;
BEGIN
INSERT INTO grandparent(g) VALUES (p_g) RETURNING id INTO v_grandparent_id;
FOREACH v_parent IN ARRAY p_parents_array
LOOP
RAISE NOTICE 'v_parent=%',v_parent;
INSERT INTO parent (p, grandparent_id) VALUES (v_parent.p, v_grandparent_id) RETURNING id INTO v_parent_id;
FOREACH v_child IN ARRAY v_parent.children
LOOP
RAISE NOTICE 'v_child=%',v_child;
INSERT INTO child (
id,
c,
parent_id
) VALUES (
v_child.i,
v_child.c,
v_parent_id
);
END LOOP; -- children loop
END LOOP; -- parents loop
END;
$$
LANGUAGE 'plpgsql';
When I use the pg8000 native connection it works as expected:
import pg8000.native
con = pg8000.native.Connection(host="localhost", database="mydb", user="myuser", password="mypass")
sql = """CALL insert_family(
'Cronus',
array[('Zeus', array[(1, 'Hestia'), (2, 'Hades')]::child_type[])]::parent_type[]
);"""
con.run(sql)
but I cannot find a way to pass the nested array using pg8000.connect with string interpolation:
sql = """CALL insert_family(
%s,
%s::parent_type[]
);"""
with pg8000.connect( host="localhost", database="mydb", user="myuser", password="mypass") as con:
with con.cursor() as cursor:
cursor.execute(
sql,
args=(
'Cronus',
[str(('Zeus', '[(1, \'Hestia\'), (2, \'Hades\')]::child_type[]'))]
)
)
The latter throws:
pg8000.dbapi.ProgrammingError: {'S': 'ERROR', 'V': 'ERROR', 'C': '22P02', 'M': 'malformed array literal: " [(1, 'Hestia'), (2, 'Hades')]"', 'D': '"[" must introduce explicitly-specified array dimensions.', 'F': 'arrayfuncs.c', 'L': '270', 'R': 'array_in'}
Can someone please help me?