using psycopg2 to call postgres function which takes an array of a custom type

Question

I currently have a custom type in postgres:

create type times_type as
(
  start varchar,
  end   varchar,
  rate real
);

I also have a function which takes an array of times_type:

create function insert_data(times_values times_type[])

When I was using named tuples and using callproc to insert one value at a time it worked fine i.e:

 Document = namedtuple('times_type', 'start 
                             end rate')


 cur.callproc('insert_data',
                     (Document('13:00', '16:00', 12.56))

But now the function is expecting an array, so i attempted:

Document = namedtuple('times_type', 'start 
                             end rate')


 cur.callproc('insert_data',
                     ([Document('13:00', '16:00', 12.56),
                       Document('17:00', '18:00', 12.56),
                       Document('19:00', '20:00', 12.56)])

psycopg2 gives me an error that the function does not exist. Is there something you have to do when dealing with an array of custom types?

Answer 1

The problem with arrays of a composite type is that they have to be explicitly cast, otherwise Postgres see them as record[]. Use register_adapter(class, adapter).

Document = namedtuple('times_type', 'start end rate')

class DocumentAdapter:
    def __init__(self, x):
        self.adapted = psycopg2.extensions.SQL_IN(x)
    def prepare(self, conn):
        self.adapted.prepare(conn)
    def getquoted(self):
        return self.adapted.getquoted() + b'::times_type'

psycopg2.extensions.register_adapter(Document, DocumentAdapter)

cur.callproc('insert_data',
                     ([Document('13:00', '16:00', 12.56),
                       Document('17:00', '18:00', 12.56),
                       Document('19:00', '20:00', 12.56)],)) # comma added, this should be a tuple
                       
conn.commit();  # dont forget to commit