Thread: 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.
Attachment
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@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Peter Eisentraut <peter_e@gmx.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@gmx.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@gmx.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@gmx.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@gmx.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