Re: [HACKERS] getting composite types info from libpq

On Wed, Dec 15, 2010 at 6:56 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Wed, Dec 15, 2010 at 1:25 PM, Daniele Varrazzo
> <daniele.varrazzo@gmail.com> wrote:
>> Hello,
>>
>> when a query returns a composite type, the libpq PQftype() function
>> reports the oid of the "record" type. In psycopg:
>>
>>    >>> cur.execute("select (1,2)")
>>    >>> cur.description
>>    (('row', 2249, None, -1, None, None, None),)
>>
>>    test=# select typname from pg_type where oid = 2249;
>>     typname
>>    ---------
>>     record
>>
>> Is there a way to recursively retrieve the types for the record components?
>
> not without talking to the server, unless you had previously pulled
> pg_attribute data.
>
> select * from pg_attribute where attrelid = 2249;

No, there is no such info in pg_attribute: 2249 is the oid for the
type of a "generic record", not for a specific type.

> This question is more appropriate for -general, but what are you trying to do?

Added -general in copy: please remove -hackers in your reply if you
think this thread is out of place.

I'm hacking on psycopg. Currently it uses PQftype, PQfname and related
functions to inspect the PQresult received after a query in order to
build the python representation of the record. But the inspection is
"flat": if the record contains a composite structure it is currently
returned as an unparsed string:

    >>> cur.execute("select ('1'::int, current_date), current_date")
    # the date outside the record is easily parsed, for the one inside
the record
    >>> cur.fetchone()
    ('(1,2010-12-16)', datetime.date(2010, 12, 16))
    >>> cur.description  # name and oid are the first two fields
    (('row', 2249, None, -1, None, None, None),
     ('date', 1082, None, 4, None, None, None))

As the record is created on the fly, I assume there is no structure
left in the catalog for it. If I instead explicitly create the type I
see how to inspect it:

    test=> create type intdate as (an_int integer, a_date date);
    CREATE TYPE

    >>> cur.execute("select (1, current_date)::intdate, current_date")
    >>> cur.fetchone()
    ('(1,2010-12-16)', datetime.date(2010, 12, 16))
    >>> cur.description
    (('row', 650308, None, -1, None, None, None),
     ('date', 1082, None, 4, None, None, None))

    test=> select attname, atttypid from pg_attribute where attrelid = 650306;
     attname | atttypid
    ---------+----------
     an_int  |       23
     a_date  |     1082

but even in this case it seems it would take a second query to inspect
the type and even here It doesn't seem I could use
PQgetvalue/PQgetlength to read the internal components of the
composite values.

The goal would be to have the query above translated into e.g. a
nested tuple in python:

    ((1, datetime.date(2010, 12, 16), datetime.date(2010, 12, 16))

and I'd like to know:

1. do I get enough info in the PGresult to inspect anonymous composite types?
2. do I get such info for composite types for which I have schema info
in the catalog, without issuing a second query? (which I don't feel it
is a driver's job)
3. is there any libpq facility to split the string returned after a
composite types into its single components, without having to write a
parser to deal with commas and quotes?

    >>> cur.execute("select ('a'::text, 'b,c'::text, 'd''e'::text,
'f\"g'::text)")
    >>> print cur.fetchone()[0]
    (a,"b,c",d'e,"f""g")

4. are by any chance those info passed on the network, maybe available
in an internal libpq structure, but then not accessible from the libpq
interface?

Thank you very much.

-- Daniele