Re: flexi adaption/casting scheme

On Thu, Sep 20, 2012 at 10:50 PM, Tobias Oberstein
<tobias.oberstein@gmail.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