Re: Parse record type into tuple

Oh, I wrote this email yesterday and forgot to send it, sorry....

On Tue, Sep 5, 2017 at 4:40 PM, Dmytro Starosud <d(dot)starosud(at)gmail(dot)com> wrote:
> Hello guys
>
> I do following interactions with DB:
>
> In [48]: conn = psycopg2.connect("dbname=... user=... password=...")
> In [49]: cur = conn.cursor()
> In [50]: cur.execute("select (1, 2)")
> In [51]: cur.fetchone()
> Out[51]: ('(1,2)',)
>
> Is it possible to get that tuple parsed into python tuple in the same way
> array works?
>
> In [55]: cur.execute("select array[1, 2]")
> In [56]: cur.fetchone()
> Out[56]: ([1, 2],)
>
> Looks like that can be done if I register composite type for that tuple.
> But I would like it to work with any tuple.

No, the problem is that the server doesn't pass extra information of
the types in a ROW, e.g. it doesn't tell that the first type is a
number and the second is a date. With the array it returns this
information, saying e.g. that the result is an array of dates, so we
can split on the elements and parse the dates one by one. What
register_composite does is exactly that: it queries the server to
check what type is each field, stores that information, and then uses
it at parsing time to parse each element correctly.

You can definitely write a typecaster yourself and assign it to the
`record` type

=# select pg_typeof((1,2));
pg_typeof
-----------
record

=# select pg_typeof((1,2))::int;
pg_typeof
-----------
2249

you can check the docs about registering a new typecaster: you will
have a function receiving '(1,2)' and you can parse it any way you
want. What you have to solve is this ambiguity though:

=# select (1,'2');
row
-------
(1,2)

your program must be able to tell whether the second element is a
string or a number. The server won't tell it and psycopg will not
punt. You may have this knowledge in your program, so you can actually
do something this way.

-- Daniele