1

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?

Javide
  • 2,477
  • 5
  • 45
  • 61
  • Is this still a problem with pg8000 version 1.29.6? Before this, pg8000 didn't support sending composite types. Another point: your two examples differ in that the first uses an SQL string, while the second uses parameters. Perhaps edit your question just to use parameters? – Tony Locke Jun 01 '23 at 08:11

0 Answers0