def create_array_elem(elem, sql_datatype, conn_info):
if elem is None:
return None
with post_db.open_connection(conn_info) as conn:
with conn.cursor() as cur:
if sql_datatype == "bit[]":
cast_datatype = "boolean[]"
elif sql_datatype == "boolean[]":
cast_datatype = "boolean[]"
elif sql_datatype == "character varying[]":
cast_datatype = "character varying[]"
elif sql_datatype == "cidr[]":
cast_datatype = "cidr[]"
elif sql_datatype == "citext[]":
cast_datatype = "text[]"
elif sql_datatype == "date[]":
cast_datatype = "text[]"
elif sql_datatype == "double precision[]":
cast_datatype = "double precision[]"
elif sql_datatype == "hstore[]":
cast_datatype = "text[]"
elif sql_datatype == "integer[]":
cast_datatype = "integer[]"
elif sql_datatype == "bigint[]":
cast_datatype = "bigint[]"
elif sql_datatype == "inet[]":
cast_datatype = "inet[]"
elif sql_datatype == "json[]":
cast_datatype = "text[]"
elif sql_datatype == "jsonb[]":
cast_datatype = "text[]"
elif sql_datatype == "macaddr[]":
cast_datatype = "macaddr[]"
elif sql_datatype == "money[]":
cast_datatype = "text[]"
elif sql_datatype == "numeric[]":
cast_datatype = "text[]"
elif sql_datatype == "real[]":
cast_datatype = "real[]"
elif sql_datatype == "smallint[]":
cast_datatype = "smallint[]"
elif sql_datatype == "text[]":
cast_datatype = "text[]"
elif sql_datatype in ("time without time zone[]", "time with time zone[]"):
cast_datatype = "text[]"
elif sql_datatype in ("timestamp with time zone[]", "timestamp without time zone[]"):
cast_datatype = "text[]"
elif sql_datatype == "uuid[]":
cast_datatype = "text[]"
else:
# custom datatypes like enums
cast_datatype = "text[]"
sql_stmt = """SELECT $stitch_quote${}$stitch_quote$::{}""".format(elem, cast_datatype)
cur.execute(sql_stmt)
res = cur.fetchone()[0]
return res
def register_type_adapters(conn_config):
"""
//todo doc needed
"""
with post_db.open_connection(conn_config) as conn:
with conn.cursor(cursor_factory=psycopg2.extras.DictCursor) as cur:
# citext[]
cur.execute(
"SELECT typarray FROM pg_type where typname = 'citext'")
citext_array_oid = cur.fetchone()
if citext_array_oid:
psycopg2.extensions.register_type(
psycopg2.extensions.new_array_type(
(citext_array_oid[0], ), 'CITEXT[]', psycopg2.STRING))
# bit[]
cur.execute("SELECT typarray FROM pg_type where typname = 'bit'")
bit_array_oid = cur.fetchone()[0]
psycopg2.extensions.register_type(
psycopg2.extensions.new_array_type((bit_array_oid, ), 'BIT[]',
psycopg2.STRING))
# UUID[]
cur.execute("SELECT typarray FROM pg_type where typname = 'uuid'")
uuid_array_oid = cur.fetchone()[0]
psycopg2.extensions.register_type(
psycopg2.extensions.new_array_type((uuid_array_oid, ),
'UUID[]', psycopg2.STRING))
# money[]
cur.execute("SELECT typarray FROM pg_type where typname = 'money'")
money_array_oid = cur.fetchone()[0]
psycopg2.extensions.register_type(
psycopg2.extensions.new_array_type((money_array_oid, ),
'MONEY[]', psycopg2.STRING))
# json and jsonb
# pylint: disable=unnecessary-lambda
psycopg2.extras.register_default_json(loads=lambda x: str(x))
psycopg2.extras.register_default_jsonb(loads=lambda x: str(x))
# enum[]'s
cur.execute(
"SELECT distinct(t.typarray) FROM pg_type t JOIN pg_enum e ON t.oid = e.enumtypid"
)
for oid in cur.fetchall():
enum_oid = oid[0]
psycopg2.extensions.register_type(
psycopg2.extensions.new_array_type(
(enum_oid, ), 'ENUM_{}[]'.format(enum_oid),
psycopg2.STRING))
