Re: PL/Python result metadata

There is currently no reliable way to retrieve from a result object in
PL/Python the number, name, or type of the result columns. You can get
the number and name if the query returned more than zero rows by looking
at the row dicts, but that is unreliable. The type information isn't
available at all.

I propose to add two functions to the result object:

.colnames() returns a list of column names (strings)
.coltypes() returns a list of type OIDs (integers)

I just made that up because there is no guidance in the other standard
PLs for this sort of thing, AFAICT.

Patch attached. Comments welcome.

Excerpts from Peter Eisentraut's message of mié ene 11 17:05:34 -0300 2012:
> There is currently no reliable way to retrieve from a result object in
> PL/Python the number, name, or type of the result columns. You can get
> the number and name if the query returned more than zero rows by looking
> at the row dicts, but that is unreliable. The type information isn't
> available at all.
>
> I propose to add two functions to the result object:
>
> .colnames() returns a list of column names (strings)
> .coltypes() returns a list of type OIDs (integers)

No typmods?

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> .colnames() returns a list of column names (strings)
> .coltypes() returns a list of type OIDs (integers)
>
> I just made that up because there is no guidance in the other standard
> PLs for this sort of thing, AFAICT.

What about having the same or comparable API as in psycopg or DB API

http://initd.org/psycopg/docs/cursor.html

You could expose a py.description structure?
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

On 11/01/12 22:52, Dimitri Fontaine wrote:
> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
>> .colnames() returns a list of column names (strings)
>> .coltypes() returns a list of type OIDs (integers)
>>
>> I just made that up because there is no guidance in the other standard
>> PLs for this sort of thing, AFAICT.
>
> What about having the same or comparable API as in psycopg or DB API
>
> http://initd.org/psycopg/docs/cursor.html
>
> You could expose a py.description structure?

+1 for providing a read-only result.description. Not sure if it's worth
it to follow DB-API there, but maybe yes. Perhaps we could have a
result.description_ex information that's PG-specific or just not present
in PEP 249, like the typmod, collation and so on.

J

On ons, 2012-01-11 at 17:16 -0300, Alvaro Herrera wrote:
> > I propose to add two functions to the result object:
> >
> > .colnames() returns a list of column names (strings)
> > .coltypes() returns a list of type OIDs (integers)
>
> No typmods?

Didn't think about that, but could be added using similar interface and
code.

On ons, 2012-01-11 at 22:52 +0100, Dimitri Fontaine wrote:
> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> > .colnames() returns a list of column names (strings)
> > .coltypes() returns a list of type OIDs (integers)
> >
> > I just made that up because there is no guidance in the other standard
> > PLs for this sort of thing, AFAICT.
>
> What about having the same or comparable API as in psycopg or DB API
>
> http://initd.org/psycopg/docs/cursor.html
>
> You could expose a py.description structure?

I deliberately chose not to do that, because the PL/Python API is
intentionally totally different from the standard DB-API, and mixing in
some semi-conforming look-alike would be quite confusing from both ends.
I think we should stick with the PL/Python API being a small layer on
top of SPI, and let the likes of plpydbapi handle the rest.

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> I deliberately chose not to do that, because the PL/Python API is
> intentionally totally different from the standard DB-API, and mixing in
> some semi-conforming look-alike would be quite confusing from both ends.

Fair enough.

> I think we should stick with the PL/Python API being a small layer on
> top of SPI, and let the likes of plpydbapi handle the rest.

I'm discovering that, and again, fair enough :)

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

At 2012-01-11 22:05:34 +0200, peter_e(at)gmx(dot)net wrote:
>
> I propose to add two functions to the result object:
>
> .colnames() returns a list of column names (strings)
> .coltypes() returns a list of type OIDs (integers) […]
>
> Patch attached. Comments welcome.

Applies, builds, passes tests. Code looks simple and good. Ready for
committer, unless you want to add a typmod accessor too.

-- ams