Thread: BUG #5272: PL/Python SELECT: return composite fields as dict, not str

The following bug has been logged online:

Bug reference:      5272
Logged by:          Steve White
Email address:      swhite@aip.de
PostgreSQL version: 8.4.2
Operating system:   Linux
Description:        PL/Python SELECT: return composite fields as dict, not
str
Details:

Hi,

As recently posted in the mailing list,
http://archives.postgresql.org/pgsql-general/2010-01/msg00389.php
(where you will find a test file, not reproduced here.):

The plpy.execute() command on a SELECT returns a list of nice dictionaries
keyed on field names, containing the fields.  For numeric types, the type of
the dictionary values are as expected.  Unfortunately however, if a field
contains a composite type, it is flattened to a string.

I would have expected a composite type field to be returned as a dictionary
of values of the proper types, keyed on the names of the elements of the
composite type.  After all, it's a DB system that supports hierarchies, and
a programming language that beautifully supports them.

In my case, I was able to work around this problem in an ugly way, but I can
imagine cases where this would render PL/Python unsuitable.

I see nothing in the documentation about this
    http://www.postgresql.org/docs/8.4/static/plpython.html
It only talks about passing composite types into and out of functions.

This is an unpleasant discovery for those working to a deadline.  Perhaps a
"limitations" section for the doc would be in order.  (You could also list
the limitations of PL/Python regarding returning RECORD types.)

But the best thing to do would be to fix it.

For backward compatibility, you might implement a switch that turns on the
hierarchical interpretation of fields, and leave the current behaviour as
the default.

Cheers!

On Mon, Jan 11, 2010 at 11:41 AM, Steve White <swhite@aip.de> wrote:
> I see nothing in the documentation about this
> =A0 =A0 =A0 =A0http://www.postgresql.org/docs/8.4/static/plpython.html
> It only talks about passing composite types into and out of functions.
>
> This is an unpleasant discovery for those working to a deadline. =A0Perha=
ps a
> "limitations" section for the doc would be in order. =A0(You could also l=
ist
> the limitations of PL/Python regarding returning RECORD types.)

Maybe you could suggest some text, perhaps in the form of a context
diff against the current sgml files?

> But the best thing to do would be to fix it.

Doubtless.  Of course it will depend on whether anyone finds enough
round tuits.  Peter Eisentraut made some improvements to PL/python
array handling which will be in the next major release, but I am not
aware of anyone working on record types.  This is an all-volunteer
effort, so people tend to scratch their own itches.

> For backward compatibility, you might implement a switch that turns on the
> hierarchical interpretation of fields, and leave the current behaviour as
> the default.

I suspect we wouldn't bother with that.

...Robert

Hi all,

Robert suggested that I start some documentation about limitations of
PL/Python.  Find attached.

I think it would also be good to explain the meaning of "trusted" regarding
PL/Python, and how it might impact the user.  There is already some
commented-out doc, and also something in the PL/Perl section.
Specifically, a user might want to know if it is actually dangerous to
use PL/Python, etc.  If it is dangerous, how, and for whom?  Where should
one be careful?

Cheers!

On 11.01.10, Robert Haas wrote:
> On Mon, Jan 11, 2010 at 11:41 AM, Steve White <swhite@aip.de> wrote:
> > I see nothing in the documentation about this
> >        http://www.postgresql.org/docs/8.4/static/plpython.html
> > It only talks about passing composite types into and out of functions.
> >
> > This is an unpleasant discovery for those working to a deadline.  Perhaps a
> > "limitations" section for the doc would be in order.  (You could also list
> > the limitations of PL/Python regarding returning RECORD types.)
>
> Maybe you could suggest some text, perhaps in the form of a context
> diff against the current sgml files?
>

--
| -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -
| Steve White                                             +49(331)7499-202
| e-Science / AstroGrid-D                                   Zi. 35  Bg. 20
| -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -
| Astrophysikalisches Institut Potsdam (AIP)
| An der Sternwarte 16, D-14482 Potsdam
|
| Vorstand: Prof. Dr. Matthias Steinmetz, Peter A. Stolz
|
| Stiftung privaten Rechts, Stiftungsverzeichnis Brandenburg: III/7-71-026
| -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -

On sön, 2010-01-31 at 13:20 +0100, Steve White wrote:
> Robert suggested that I start some documentation about limitations of
> PL/Python.  Find attached.
>
> I think it would also be good to explain the meaning of "trusted" regarding
> PL/Python, and how it might impact the user.  There is already some
> commented-out doc, and also something in the PL/Perl section.
> Specifically, a user might want to know if it is actually dangerous to
> use PL/Python, etc.  If it is dangerous, how, and for whom?  Where should
> one be careful?

I have added some documentation for both of these issues along the lines
suggested by you.