MIME-Version: 1.0
From: Vladimir Ryabtsev <greatvovan@gmail.com>
Date: Tue, 10 Nov 2020 13:59:31 -0800
Message-ID: 
 <CAMqTPqm7H4yVk0qGA8=TyTtVsNX88bo8x1BJkHc6vrOVYyW=RQ@mail.gmail.com>
Subject: Using composite types in psycopg3
To: psycopg@postgresql.org
Content-Type: multipart/alternative; boundary="000000000000cbc78805b3c7cbc6"
Precedence: bulk

--000000000000cbc78805b3c7cbc6
Content-Type: text/plain; charset="UTF-8"

Hello,

I have tried psycopg3 very briefly and I have a question.

I have a couple of use cases in systems I currently work with
that prevented (or seriously limited) usage of psycopg2, so
I had to use other drivers. This generally relates to accessing
composite types that you can construct on-the-fly in SQL queries.
Consider the following example:

'''
import psycopg2

def get_query(fpath):
    with open(fpath, 'rt') as f:
        return f.read()

def main():
    conn = psycopg2.connect('postgres://user:password@host/db')
    cur = conn.cursor()
    cur.execute(get_query('query.sql'))
    result = cur.fetchone()[0]
    print(type(result), result)
    cur.close()
    conn.close()

if __name__ == '__main__':
    main()
'''

Where query.sql is:

'''
with test as (
    select 1 as id, 'one' val
    union all
    select 1, 'one more'
    union all
    select 2, 'two'
)
select array(
    select (id, array_agg(val))
    from test
    group by id
)
'''

psycopg2 returns the 'result' as a basic string, while
in asyncpg and py-postgresql I have structured data
(roughly 'List[Tuple[int, List[str]]]').

I tried the same in psycopg3 and it is little bit better, but
not entirely: it shows the outer list, the tuples inside it,
but the innermost list is still represented as a basic string:
'{one,"one more"}'.

Is it something you are still working on? Any workarounds?

Vladimir

--000000000000cbc78805b3c7cbc6
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hello,<div><br></div><div>I have tried psycopg3 very brief=
ly and I have a question.</div><div><br></div><div>I have a couple=C2=A0of =
use cases in systems I currently work with</div><div>that prevented (or ser=
iously limited) usage of psycopg2, so</div><div>I had to use other drivers.=
 This generally relates to accessing</div><div>composite types that you can=
 construct on-the-fly in SQL queries.</div><div>Consider the following exam=
ple:</div><div><br></div><div>&#39;&#39;&#39;</div><div>import psycopg2<br>=
<br>def get_query(fpath):<br>=C2=A0 =C2=A0 with open(fpath, &#39;rt&#39;) a=
s f:<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 return f.read()<br><br>def main():<br>=
=C2=A0 =C2=A0 conn =3D psycopg2.connect(&#39;postgres://user:password@host/=
db&#39;)<br>=C2=A0 =C2=A0 cur =3D conn.cursor()<br>=C2=A0 =C2=A0 cur.execut=
e(get_query(&#39;query.sql&#39;))<br>=C2=A0 =C2=A0 result =3D cur.fetchone(=
)[0]<br>=C2=A0 =C2=A0 print(type(result), result)<br>=C2=A0 =C2=A0 cur.clos=
e()<br>=C2=A0 =C2=A0 conn.close()<br><br>if __name__ =3D=3D &#39;__main__&#=
39;:<br>=C2=A0 =C2=A0 main()<br></div><div>&#39;&#39;&#39;</div><div><br></=
div><div>Where query.sql is:</div><div><br></div><div>&#39;&#39;&#39;</div>=
<div>with test as (<br>=C2=A0 =C2=A0 select 1 as id, &#39;one&#39; val<br>=
=C2=A0 =C2=A0 union all<br>	=C2=A0 =C2=A0 select 1, &#39;one more&#39;<br>	=
=C2=A0 =C2=A0 union all<br>	=C2=A0 =C2=A0 select 2, &#39;two&#39;<br>)<br>s=
elect array(<br>=C2=A0 =C2=A0 select (id, array_agg(val))<br>	=C2=A0 =C2=A0=
 from test<br>	=C2=A0 =C2=A0 group by id<br>)<br></div><div>&#39;&#39;&#39;=
</div><div><br></div><div>psycopg2 returns the &#39;result&#39; as a basic =
string, while</div><div>in asyncpg and py-postgresql I have structured data=
</div><div>(roughly &#39;List[Tuple[int, List[str]]]&#39;).</div><div><br><=
/div><div>I tried the same in psycopg3 and it is little bit better, but</di=
v><div>not entirely: it shows the outer list, the tuples inside it,</div><d=
iv>but the innermost list is still represented as a basic string:</div><div=
>&#39;{one,&quot;one more&quot;}&#39;.<br></div><div><br></div><div>Is it s=
omething you are still working on? Any workarounds?</div><div><br></div><di=
v>Vladimir</div></div>

--000000000000cbc78805b3c7cbc6--