Re: [psycopg] Solving the SQL composition problem

On 1/3/17 10:35 AM, Adrian Klaver wrote:
>> What I'm suggesting is to let Postgres handle the replacement of normal
>> values, using the prepared statement syntax of $1, $2, etc[1], and only
>> do identifier replacement in python (using quote_ident). That means a
>> lot less time spent parsing, and opens the door for eventually doing
>> more efficient stuff over the wire, like using binary type formats.
>
> I believe a path to the above is explained here:
>
> https://www.postgresql.org/message-id/AANLkTi=ym3SCQKCQBtp8RJHUswwAPOpjXYKTXS=aHWzp@mail.gmail.com

Seems reasonable at first glance.

Speaking of type compatibility and what-not... I find it irritating that
we need to maintain that in two different places (psycopg2 and
plpython). It'd be damn nice if we could abstract that out somehow, as
well as fixing plpython's brain-dead behavior with nested types (arrays
of complex, or complex with arrays or more complex types).

I've also been mucking around with having the SPI functions in plpython
return something that looks more like a dataframe. I've got working code
but have yet to figure out a good interface. Part of that is trying to
figure out how to deal with a bunch of different possible arguments in
the C code. I'm thinking the most flexible API would be something like

plpy.execute(..., container=[], members={})

to match existing behavior. You could then do container={}, members=[]
to get something that looks like a dataframe. Or (theoretically) you
could pass in the desired Pandas types (I think that'd be
contairner=pd.DataFrame, members=pd.Series).
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)