Re: flexi adaption/casting scheme

On Thu, Sep 20, 2012 at 10:50 PM, Tobias Oberstein
<tobias(dot)oberstein(at)gmail(dot)com> wrote:
> Hi,
>
> I'd like to implement a certain type adaption/casting scheme
> and struggle with the right approach. Maybe someone can give
> me a hint? Would be great and welcome!
>
> Context: I am _only_ concerned with calling stored procedures,
> so there is the complete type metadata from PG catalog regarding
> the PG side available to use for mapping.

As long as you know a type oid you can associate a different cast function.
>
>
> PG => Py (typecasting):
> -----------------------
>
> 1.1
> hstore => plain Python dict

register_hstore does this

> 1.2
> JSON => json.loads() .. whatever Python object that gives

register_json will do

> 1.3
> composite type => plain Python dict with key/value pairs only
> for all attributes in the composite type that have non-NULL values

register_composite will return a namedtuple: it should be easy to
change that code and return a dict instead.

> 1.4
> everything else => as per-default with Psycopg

> Py => PG (adaption):

Adaptation only works on the class of the Python object: there is no
knowledge about the target type in postgres.

> 2.1
> plain Python dict ..:
>
> 2.1.1
> PG target type = hstore => dict-to-hstore with conversion of keys/values to
> str repr. if needed

you may subclass dict into an "hstore" python type and maybe guard
key/values accepting only strings, or converting the items according
to your rules.

> 2.1.2
> PG target type = JSON => json.dumps() whatever str that produces

same as hstore, with the extra nuance that the py object may be a
dict, a list and much more.

> 2.1.3
> PG target type = composite type => for every key in the Python dict that is
> an attribute in the composite type, fill in the value from the dict; for
> every attribute in the composite type where there is no key in the Python
> dict, fill in NULL

You can do it, but again you will need a dict subclass for it.

> 2.2
> everything else => as per-default with Psycopg
>
> ==
>
> Above should work with nested PG types (array of composite type with
> an attribute again composite type etc etc).

The composite caster already deals correctly with nested types: the
change to make it return dicts instead of tuples should be a few lines
of code.

> It should work with IN, OUT, INOUT parameters and array, setof, etc
> returning procedures.

Procedures with OUT parameters don't return a specific oid: they
return a generic "record" oid (2249).

test=> create or replace function mysp (out a int, out b text) as
$$select 10::int, 'ciao'::text;$$ language sql;
CREATE FUNCTION
test=> select mysp(), pg_typeof(mysp());
mysp | pg_typeof
-----------+-----------
(10,ciao) | record
(1 row)

If you want to return the identity of that record, you will have to
create a Postgres type and cast your function to that record: you can
then register a typecaster against its oid.

test=> create type myspout as (a int, b text);
CREATE TYPE

-- Don't know if there's a better syntax for this
test=> select (x.*)::myspout from mysp() as x;
x
-----------
(10,ciao)
(1 row)

In Python:

In [2]: cnn = psycopg2.connect('dbname=test')

In [5]: psycopg2.extras.register_composite('myspout', cnn)
Out[5]: <psycopg2.extras.CompositeCaster object at 0x8fd672c>

In [6]: cur = cnn.cursor()

In [7]: cur.execute("select (x.*)::myspout from mysp() as x;")

In [8]: cur.fetchone()[0]
Out[8]: myspout(a=10, b='ciao')

> How do I tackle this? Or even more fundamental: is it sane / doable at all
> (using public Psycopg hooks only)?

The only impossible thing is the "PG target type" idea: there is no
knowledge about where a parameter will go in a query.

Hope this helps.

-- Daniele