Thread: plpython feature idea: an option to return row results as lists

With result rows in plpython returned as dicts rather than lists, we ran
into issues with a need to preserve the column order in the resultset.
Of course, dicts in python have an arbitrary, non-random order. It's
consistent in the result but does not match the order in the query. Our
use case was a third party to a customer and ourselves who required a
specific order of columns in CSVs sent to them.

Has there ever been any interest in adding a keyword option for
returning row lists rather than dicts? I couldn't find any, so I
experimented a little and came up with the attached patch. I tested this
a little...managed to make it not segfault at the very least. :)  I'm
not even close to a guru so there's probably at least one mistake.

As an example:

test=# do language plpythonu $$
a = plpy.execute("""
     SELECT 1 as a, 2 as b, NULL as c, ARRAY[1,2,3] as d;
     """, return_list=True)
for row in a:
     plpy.notice(repr(row))
$$;
NOTICE:  [1, 2, None, [1, 2, 3]]
CONTEXT:  PL/Python anonymous code block
DO

I didn't test with Python 3/plpython3u.

On Fri, Jul 30, 2010 at 15:45, Derek Arnold
<derek.arnold@dealerbuilt.com> wrote:
> With result rows in plpython returned as dicts rather than lists, we ran
> into issues with a need to preserve the column order in the resultset.

Interesting, +1 for the idea.

> plpy.execute("""
>   SELECT 1 as a, 2 as b, NULL as c, ARRAY[1,2,3] as d;
>   """, return_list=True)

Hrm... Maybe a separate function is better. I dont think that will
translate to other languages very well.  For instance pl/perl, you
would end up doing something like spi_exec_query("select 1;",
undefined, {return_list=>1}); ick.  Yes we could make spi_exec_query()
say oh the 2nd arg is a hash? well then its the options.  Seems
fragile, and i dunno about other pls.

FYI, I did look at pl/perl and pl/tcl and they seem to be able to only
return "dictionaries" as well.

On fre, 2010-07-30 at 16:45 -0500, Derek Arnold wrote:
> Has there ever been any interest in adding a keyword option for
> returning row lists rather than dicts?

I don't think so, but it sounds like a reasonable idea.  Other possible
approaches are

- Using a factory class like psycopg
(http://initd.org/psycopg/docs/extras.html)

- Using an OrderedDict