Re: Parse record type into tuple

Thanks guys for assistance.
Looks like the most easy solution *to use* will be the one which is hardest
to implement: write parser of tuple and hope PG will eventually fully
support generics :)

Cheers
Dmytro

2017-09-06 14:10 GMT+03:00 Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com>:

> 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
>