Thread: flexi adaption/casting scheme

flexi adaption/casting scheme

From
Tobias Oberstein
Date:
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.


PG => Py (typecasting):
-----------------------

1.1
hstore => plain Python dict

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

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

1.4
everything else => as per-default with Psycopg


Py => PG (adaption):
--------------------

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

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

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

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).

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

==

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

Thanks alot,
Tobias



Re: flexi adaption/casting scheme

From
Daniele Varrazzo
Date:
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


Re: flexi adaption/casting scheme

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

It would be great to have an API to customize the class to instantiate with a
composite type.

I tried an implementation in the attached patch.

The idea would be to add an optional "ctor" argument, which would be used in
place of the namedtuple argument. Additionally, an adapter can be
automatically registered to perform the reverse conversion.

Regards,

--
Ronan Dunklau

Attachment

Re: flexi adaption/casting scheme

From
Tobias Oberstein
Date:
Hi Daniel,

thanks for your hints .. I have learned some things!

>> 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.

Apparently I failed to articulate what I try to do: yes, adjusting
the CompositeCaster to _return_ dicts instead of tuples is a snap
replacing

         return self._ctor(*attrs)

with

         for i in xrange(len(self.atttypes)):
             if attrs[i] is not None:
                 o[self.attnames[i]] = attrs[i]
         return o

in CompositeCaster.parse.

(sidenote: somewhat inconvenient is the fact that I need to duplicate
the code .. cannot derive from the class, since the
CompositeCaster._from_db class method will always construct a
CompositeCaster, not my derived class).

What I am struggling with is the _opposite_ direction: have Python
dicts automatically adapt to composite types (when knowing the
respective target type).

In the meantime I have gotten quite close:

https://github.com/oberstet/scratchbox/blob/master/python/psycopg2/test6.py

Nested arrays of composite types still don't work ..

Cheers,
Tobias


Re: flexi adaption/casting scheme

From
Tobias Oberstein
Date:
Could someone help me out: whats wrong with this short snippet?

https://github.com/oberstet/scratchbox/blob/master/python/psycopg2/test7.py


Traceback (most recent call last):
   File "test7.py", line 29, in <module>
     cur.execute("SELECT test_employee(%s)", [v1])
psycopg2.ProgrammingError: cannot cast type record to t_address
LINE 1: ...4, ARRAY[1, 2, 3], NULL, ('Duckhausen', NULL, 18, ARRAY[(10,...
                                                              ^
DETAIL:  Cannot cast type record[] to t_station[] in column 4.

Thanks alot,
Tobias


Re: flexi adaption/casting scheme

From
Daniele Varrazzo
Date:
On Fri, Sep 21, 2012 at 2:47 PM, Ronan Dunklau <rdunklau@gmail.com> wrote:
>> 1.3
>> composite type => plain Python dict with key/value pairs only
>> for all attributes in the composite type that have non-NULL values
>
> It would be great to have an API to customize the class to instantiate with a
> composite type.
>
> I tried an implementation in the attached patch.
>
> The idea would be to add an optional "ctor" argument, which would be used in
> place of the namedtuple argument. Additionally, an adapter can be
> automatically registered to perform the reverse conversion.

Thank you, I like the idea of customizing the composite caster. But I
don't like very much the proposed way. Oddly enough, just yesterday
I've changed the _ctor: it used to take *args instead of an iterable
of args but I've just recently discovered namedtuple._make which has
the same signature of the basic tuple and can be used to avoid
unpacking. You patch puts it back to "self._ctor = lambda *args:
tuple(args)" and then some.

Your patch passes the arguments to the ctor in the most generic way
(as **kwargs) in order to work with any possible function, but this is
overkilling for the basic use with tuple/namedtuple. And still
wouldn't be enough: you can't make an OrderedDict out of it for
instance, as **kwargs randomizes the order.

I propose the attached diff instead. It makes the CompositeCaster easy
to subclass and exposes part of the machinery used by
register_composite() in order to make the class registrable without
adding new arguments to register_composite() too, which would only
deal with the basic case. Customization is performed overriding the
method "make" which takes the attributes read from the db in input.
Attribute names can be obtained from "self".

For example, to cast composite to dictionaries one can subclass it as:

    class DictComposite(psycopg2.extras.CompositeCaster):
        def make(self, attrs):
            return dict(zip(self.attnames, attrs))

Which would be used as:

    # from psql: CREATE TYPE card AS (value int, suit text);

    c = DictComposite.from_db('card', cnn)
    c.register()

    cur.execute("select (8, 'hearts')::card")
    cur.fetchone()[0]
    {'suit': 'hearts', 'value': 8}

Seems nice, doesn't it?


Tobias: as expected it works ok with composite types, as the original
CompositeCaster does:

    # from psql: CREATE TYPE card_back AS (face card, back text);

    c2 = DictComposite.from_db('card_back', cnn)
    c2.register()

    cur.execute("select ((8, 'hearts'), 'blue')::card_back")
    cur.fetchone()[0]
    {'back': 'blue', 'face': {'suit': 'hearts', 'value': 8}}

if it doesn't work for you, you are probably doing something wrong.

-- Daniele

Attachment

Re: flexi adaption/casting scheme

From
Ronan Dunklau
Date:
Le vendredi 21 septembre 2012 17:04:01 Daniele Varrazzo a écrit :
> On Fri, Sep 21, 2012 at 2:47 PM, Ronan Dunklau <rdunklau@gmail.com> wrote:
> >> 1.3
> >> composite type => plain Python dict with key/value pairs only
> >> for all attributes in the composite type that have non-NULL values
> >
> > It would be great to have an API to customize the class to instantiate
> > with a composite type.
> >
> > I tried an implementation in the attached patch.
> >
> > The idea would be to add an optional "ctor" argument, which would be used
> > in place of the namedtuple argument. Additionally, an adapter can be
> > automatically registered to perform the reverse conversion.
>
> Thank you, I like the idea of customizing the composite caster. But I
> don't like very much the proposed way. Oddly enough, just yesterday
> I've changed the _ctor: it used to take *args instead of an iterable
> of args but I've just recently discovered namedtuple._make which has
> the same signature of the basic tuple and can be used to avoid
> unpacking. You patch puts it back to "self._ctor = lambda *args:
> tuple(args)" and then some.
>
> Your patch passes the arguments to the ctor in the most generic way
> (as **kwargs) in order to work with any possible function, but this is
> overkilling for the basic use with tuple/namedtuple. And still
> wouldn't be enough: you can't make an OrderedDict out of it for
> instance, as **kwargs randomizes the order.
>
> I propose the attached diff instead. It makes the CompositeCaster easy
> to subclass and exposes part of the machinery used by
> register_composite() in order to make the class registrable without
> adding new arguments to register_composite() too, which would only
> deal with the basic case. Customization is performed overriding the
> method "make" which takes the attributes read from the db in input.
> Attribute names can be obtained from "self".
>
> For example, to cast composite to dictionaries one can subclass it as:
>
>     class DictComposite(psycopg2.extras.CompositeCaster):
>         def make(self, attrs):
>             return dict(zip(self.attnames, attrs))
>
> Which would be used as:
>
>     # from psql: CREATE TYPE card AS (value int, suit text);
>
>     c = DictComposite.from_db('card', cnn)
>     c.register()
>
>     cur.execute("select (8, 'hearts')::card")
>     cur.fetchone()[0]
>     {'suit': 'hearts', 'value': 8}
>
> Seems nice, doesn't it?

It does indeed seem nice :).
I would  be very happy if it is included in a future release.

My use case could then be implemented as the following simple mixin:

class ClassComposite(psycopg2.extras.CompositeCaster):
   def make(self, attrs):
    return self.__class__(**dict(zip(self.attnames, attrs)))

Thank you for the correction and the detailed explanation.

Best Regards,

--
Ronan Dunklau






>
> Tobias: as expected it works ok with composite types, as the original
> CompositeCaster does:
>
>     # from psql: CREATE TYPE card_back AS (face card, back text);
>
>     c2 = DictComposite.from_db('card_back', cnn)
>     c2.register()
>
>     cur.execute("select ((8, 'hearts'), 'blue')::card_back")
>     cur.fetchone()[0]
>     {'back': 'blue', 'face': {'suit': 'hearts', 'value': 8}}
>
> if it doesn't work for you, you are probably doing something wrong.
>
> -- Daniele


Re: flexi adaption/casting scheme

From
Tobias Oberstein
Date:
Hi Daniele,

> For example, to cast composite to dictionaries one can subclass it as:
>
>      class DictComposite(psycopg2.extras.CompositeCaster):
>          def make(self, attrs):
>              return dict(zip(self.attnames, attrs))
>

This is nifty! I can then just slightly adjust above to filter for attrs
which are not None (only include those into the dict).


> Tobias: as expected it works ok with composite types, as the original
> CompositeCaster does:
>
>      # from psql: CREATE TYPE card_back AS (face card, back text);
>
>      c2 = DictComposite.from_db('card_back', cnn)
>      c2.register()
>
>      cur.execute("select ((8, 'hearts'), 'blue')::card_back")
>      cur.fetchone()[0]
>      {'back': 'blue', 'face': {'suit': 'hearts', 'value': 8}}
>
> if it doesn't work for you, you are probably doing something wrong.

I am struggling with _adaption_ (Python dict => PG composite), not
_typecasting_ (PG composite => dict).

My understanding would be that the following code should work with an
unmodified Psycopg, but it does not:

https://github.com/oberstet/scratchbox/blob/master/python/psycopg2/test7.py

What am I doing wrong?

Thanks!
Tobias



Re: flexi adaption/casting scheme

From
Daniele Varrazzo
Date:
On Fri, Sep 21, 2012 at 6:07 PM, Tobias Oberstein
<tobias.oberstein@gmail.com> wrote:

> My understanding would be that the following code should work with an
> unmodified Psycopg, but it does not:
>
> https://github.com/oberstet/scratchbox/blob/master/python/psycopg2/test7.py

Postgres fails finding a cast for this statement (that you can get
with "cur.mogrify("SELECT test_employee(%s)", [v1])"):

    SELECT test_employee(('foo', 44, ARRAY[1, 2, 3], NULL,
('Duckhausen', NULL, 18, ARRAY[(10, NULL, 'blub'), (NULL, 5,
NULL)])));

The error is "Cannot cast type record[] to t_station[]". It's too
complex or ambiguous for postgres to perform this cast. To work around
it we must cast the (10, NULL, 'blub') generic record into a
t_station. You can work around it by registering a specific adapter
for that tuple (that you should subclass e.g. in a namedtuple).

    from collections import namedtuple
    station = namedtuple('station', 'x y label')

    # this adapter invokes the basic tuple adapter and adds a specific cast.
    class StationAdapter(object):
        def __init__(self, adapted):
            self.adapted = adapted
        def prepare(self,conn):
            self._conn = conn
        def getquoted(self):
            a = psycopg2.extensions.adapt(tuple(self.adapted))
            a.prepare(self._conn)
            return a.getquoted() + '::t_station'

    psycopg2.extensions.register_adapter(station, StationAdapter)

    v1 = ('foo', 44, [1, 2, 3], None, ('Duckhausen', None, 18,
[station(10, None, 'blub'), station(None, 5, None)]))
    print cur.mogrify("SELECT test_employee(%s)", [v1])

returns:

    SELECT test_employee(('foo', 44, ARRAY[1, 2, 3], NULL,
('Duckhausen', NULL, 18, ARRAY[(10, NULL, 'blub')::t_station, (NULL,
5, NULL)::t_station])))

Looks like for such complex cast you can't use the generic tuples but
will have to use some class of your own. This will reduce the need for
postgres to guess your types, likely making the communication more
robust.

-- Daniele


Re: flexi adaption/casting scheme

From
Tobias Oberstein
Date:
Hi Daniele,

Ah, ok. I'll adapt my "composite target type aware dict adaptor" to add
those explict casts. It has the info anyway, but I was originally
only morphing the Py dicts into Py tuples relying on the generic tuple
to composite type adapation.

Thanks again for helping,
Tobias

Am 21.09.2012 19:39, schrieb Daniele Varrazzo:
> On Fri, Sep 21, 2012 at 6:07 PM, Tobias Oberstein
> <tobias.oberstein@gmail.com> wrote:
>
>> My understanding would be that the following code should work with an
>> unmodified Psycopg, but it does not:
>>
>> https://github.com/oberstet/scratchbox/blob/master/python/psycopg2/test7.py
>
> Postgres fails finding a cast for this statement (that you can get
> with "cur.mogrify("SELECT test_employee(%s)", [v1])"):
>
>      SELECT test_employee(('foo', 44, ARRAY[1, 2, 3], NULL,
> ('Duckhausen', NULL, 18, ARRAY[(10, NULL, 'blub'), (NULL, 5,
> NULL)])));
>
> The error is "Cannot cast type record[] to t_station[]". It's too
> complex or ambiguous for postgres to perform this cast. To work around
> it we must cast the (10, NULL, 'blub') generic record into a
> t_station. You can work around it by registering a specific adapter
> for that tuple (that you should subclass e.g. in a namedtuple).
>
>      from collections import namedtuple
>      station = namedtuple('station', 'x y label')
>
>      # this adapter invokes the basic tuple adapter and adds a specific cast.
>      class StationAdapter(object):
>          def __init__(self, adapted):
>              self.adapted = adapted
>          def prepare(self,conn):
>              self._conn = conn
>          def getquoted(self):
>              a = psycopg2.extensions.adapt(tuple(self.adapted))
>              a.prepare(self._conn)
>              return a.getquoted() + '::t_station'
>
>      psycopg2.extensions.register_adapter(station, StationAdapter)
>
>      v1 = ('foo', 44, [1, 2, 3], None, ('Duckhausen', None, 18,
> [station(10, None, 'blub'), station(None, 5, None)]))
>      print cur.mogrify("SELECT test_employee(%s)", [v1])
>
> returns:
>
>      SELECT test_employee(('foo', 44, ARRAY[1, 2, 3], NULL,
> ('Duckhausen', NULL, 18, ARRAY[(10, NULL, 'blub')::t_station, (NULL,
> 5, NULL)::t_station])))
>
> Looks like for such complex cast you can't use the generic tuples but
> will have to use some class of your own. This will reduce the need for
> postgres to guess your types, likely making the communication more
> robust.
>
> -- Daniele
>



Re: flexi adaption/casting scheme

From
Daniele Varrazzo
Date:
On Fri, Sep 21, 2012 at 5:42 PM, Ronan Dunklau <rdunklau@gmail.com> wrote:

> I would  be very happy if it is included in a future release.

I've pushed it into my composite-custom branch. I've mixed our
designs, refactoring CompositeCaster to allow overriding make(), but
adding a factory parameter to register_composite() to register the
subclasses the same way the base class is usually created, without the
from_db/register methods exposed in my previous patch.

An example of usage is in the docs: see
https://github.com/dvarrazzo/psycopg/commit/fa9393b5870f07d6fb3ac55f5d90ffd8e06fe678#L1R208

Thank you and Tobias for the input. Testing and comments are welcome.

-- Daniele


Re: flexi adaption/casting scheme

From
Tobias Oberstein
Date:
Am 22.09.2012 03:21, schrieb Daniele Varrazzo:
> On Fri, Sep 21, 2012 at 5:42 PM, Ronan Dunklau <rdunklau@gmail.com> wrote:
>
>> I would  be very happy if it is included in a future release.
>
> I've pushed it into my composite-custom branch. I've mixed our
> designs, refactoring CompositeCaster to allow overriding make(), but
> adding a factory parameter to register_composite() to register the
> subclasses the same way the base class is usually created, without the
> from_db/register methods exposed in my previous patch.
>
> An example of usage is in the docs: see
> https://github.com/dvarrazzo/psycopg/commit/fa9393b5870f07d6fb3ac55f5d90ffd8e06fe678#L1R208
>
> Thank you and Tobias for the input. Testing and comments are welcome.
>
> -- Daniele
>

Hi Daniele,

Ok, I'll do testing combining the recent Json + above stuff together.
Thanks for adding nifty features!

1 thing I stumbled over (not related to above stuff):

the _from_db class method on CompositeCaster takes a name argument and
parsed that into "schema" and "typename".

It uses both to retrieve Oids etc, but then only forwards "typename",
and not "schema" to the CompositeCaster constructor.

If a have 2 composite types defined "public.t_foo" and "bar.t_foo", and
register both, one will be overwritten ..

Can we have "schema" in CompositeCaster also?

Today:

     def __init__(self, name, oid, attrs, array_oid=None):
         self.name = name



Proposed:

     def __init__(self, schema, name, oid, attrs, array_oid=None):
         self.schema = schema
         self.name = name

     @classmethod
     def _from_db(self, name, conn_or_curs):

...

         return CompositeCaster (schema, tname, type_oid, type_attrs,
             array_oid=array_oid)



Alternatively, without breaking the API:

     @classmethod
     def _from_db(self, name, conn_or_curs):

...
         c = CompositeCaster (tname, type_oid, type_attrs,
array_oid=array_oid)
         c.schema = schema
         return c


     def __init__(self, name, oid, attrs, array_oid=None):
         self.name = name
         self.schema = ''
         self.oid = oid
         self.array_oid = array_oid

         self.attnames = [ a[0] for a in attrs ]
         self.atttypes = [ a[1] for a in attrs ]

         fullname = self.schema + self.name

         self._create_type(fullname , self.attnames)
         self.typecaster = new_type((oid,), fullname, self.parse)
         if array_oid:
             self.array_typecaster = new_array_type(
                 (array_oid,), "%sARRAY" % fullname, self.typecaster)
         else:
             self.array_typecaster = None


A third alternative:

     @classmethod
     def _from_db(self, name, conn_or_curs):

...

         # forward name instead of tname !
         return CompositeCaster (name, type_oid, type_attrs,
             array_oid=array_oid)



     def __init__(self, name, oid, attrs, array_oid=None):
...
         # namedtuple's cannot have dots in name ..
         self._create_type(name.replace('.', '_'), self.attnames)
...

====

Personally I'd be fine with all .. they all prohibit clashes with
identically named composite types in different schemata and both provide
the ability to get the fully qualified composite type name in the
CompositeCaster instance.

Cheers,
Tobias


Re: flexi adaption/casting scheme

From
Daniele Varrazzo
Date:
On Sat, Sep 22, 2012 at 2:25 PM, Tobias Oberstein
<tobias.oberstein@gmail.com> wrote:

> the _from_db class method on CompositeCaster takes a name argument and
> parsed that into "schema" and "typename".
>
> It uses both to retrieve Oids etc, but then only forwards "typename", and
> not "schema" to the CompositeCaster constructor.
>
> If a have 2 composite types defined "public.t_foo" and "bar.t_foo", and
> register both, one will be overwritten ..

Uhm... why overwritten? The two CompositeCaster will register two
different typecasters on two different oids. The name is only used as
name namedtuple name. What would the schema be used for?

-- Daniele


Re: flexi adaption/casting scheme

From
Tobias Oberstein
Date:
Am 22.09.2012 15:30, schrieb Daniele Varrazzo:
> On Sat, Sep 22, 2012 at 2:25 PM, Tobias Oberstein
> <tobias.oberstein@gmail.com> wrote:
>
>> the _from_db class method on CompositeCaster takes a name argument and
>> parsed that into "schema" and "typename".
>>
>> It uses both to retrieve Oids etc, but then only forwards "typename", and
>> not "schema" to the CompositeCaster constructor.
>>
>> If a have 2 composite types defined "public.t_foo" and "bar.t_foo", and
>> register both, one will be overwritten ..
>
> Uhm... why overwritten? The two CompositeCaster will register two
> different typecasters on two different oids. The name is only used as

Ok. So new_type/new_array_type have no issue with having the same "name"
used twice on different OIDs?

> name namedtuple name. What would the schema be used for?

My use case: have a CompositeDictCaster that spits out:

{'record': 'public.t_station', 'x': 10, 'y': 8}

that is injects a 'record' field containing the composite type name.
Hence I need the schema, not only the typename.

Background:

I am experimenting with that in combination with a custom dict adapter
that is able to adapt such value again into

(10, 8, NULL)::public.t_station

so this is fully "round trippable". That way the type info is embedded
and I don't need to do complex object mangling.

The motivation for all this: I have a network server that has a
JSON/WebSocket based API for RPC (and PubSub), and I want to forward
those to PostgreSQL stored procedure calls.

Call PG stored procs from browser JS ..

Cheers,
Tobias


>
> -- Daniele
>



Re: flexi adaption/casting scheme

From
Daniele Varrazzo
Date:
On Sat, Sep 22, 2012 at 2:44 PM, Tobias Oberstein
<tobias.oberstein@gmail.com> wrote:
> Am 22.09.2012 15:30, schrieb Daniele Varrazzo:
>
>> On Sat, Sep 22, 2012 at 2:25 PM, Tobias Oberstein
>> <tobias.oberstein@gmail.com> wrote:
>>
>>> the _from_db class method on CompositeCaster takes a name argument and
>>> parsed that into "schema" and "typename".
>>>
>>> It uses both to retrieve Oids etc, but then only forwards "typename", and
>>> not "schema" to the CompositeCaster constructor.
>>>
>>> If a have 2 composite types defined "public.t_foo" and "bar.t_foo", and
>>> register both, one will be overwritten ..
>>
>>
>> Uhm... why overwritten? The two CompositeCaster will register two
>> different typecasters on two different oids. The name is only used as
>
>
> Ok. So new_type/new_array_type have no issue with having the same "name"
> used twice on different OIDs?

No, none.


>> name namedtuple name. What would the schema be used for?
>
>
> My use case: have a CompositeDictCaster that spits out:
>
> {'record': 'public.t_station', 'x': 10, 'y': 8}
>
> that is injects a 'record' field containing the composite type name.
> Hence I need the schema, not only the typename.

Makes sense. I'll see to add a schema attribute to the CompositeCaster.


-- Daniele


Re: flexi adaption/casting scheme

From
Tobias Oberstein
Date:
>> My use case: have a CompositeDictCaster that spits out:
>>
>> {'record': 'public.t_station', 'x': 10, 'y': 8}
>>
>> that is injects a 'record' field containing the composite type name.
>> Hence I need the schema, not only the typename.
>
> Makes sense. I'll see to add a schema attribute to the CompositeCaster.

Thanks!

Tobias



Re: flexi adaption/casting scheme

From
Daniele Varrazzo
Date:
On Sat, Sep 22, 2012 at 2:57 PM, Tobias Oberstein
<tobias.oberstein@gmail.com> wrote:
>>> My use case: have a CompositeDictCaster that spits out:
>>>
>>> {'record': 'public.t_station', 'x': 10, 'y': 8}
>>>
>>> that is injects a 'record' field containing the composite type name.
>>> Hence I need the schema, not only the typename.
>>
>>
>> Makes sense. I'll see to add a schema attribute to the CompositeCaster.

Added to composite-custom branch.

Would this do? https://github.com/dvarrazzo/psycopg/commit/9949e04c70386e09a4dde2371b82895b0163d9ef

-- Daniele


Re: flexi adaption/casting scheme

From
Tobias Oberstein
Date:
Am 22.09.2012 16:15, schrieb Daniele Varrazzo:
> On Sat, Sep 22, 2012 at 2:57 PM, Tobias Oberstein
> <tobias.oberstein@gmail.com> wrote:
>>>> My use case: have a CompositeDictCaster that spits out:
>>>>
>>>> {'record': 'public.t_station', 'x': 10, 'y': 8}
>>>>
>>>> that is injects a 'record' field containing the composite type name.
>>>> Hence I need the schema, not only the typename.
>>>
>>>
>>> Makes sense. I'll see to add a schema attribute to the CompositeCaster.
>
> Added to composite-custom branch.
>
> Would this do? https://github.com/dvarrazzo/psycopg/commit/9949e04c70386e09a4dde2371b82895b0163d9ef

Just merged: yep, that works for me!

Here is what I now have:

https://github.com/oberstet/scratchbox/blob/master/python/psycopg2/test8.py

Thanks!

Tobias



Re: flexi adaption/casting scheme

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

Sorry to bother you once more: is it possible to cast any PG RECORD
(anything OID 2249) into Python _lists_?

https://github.com/oberstet/scratchbox/blob/master/python/psycopg2/test9.py

?

Thanks again for helping!

Tobias


Re: flexi adaption/casting scheme

From
Daniele Varrazzo
Date:
On Sat, Sep 22, 2012 at 7:02 PM, Tobias Oberstein
<tobias.oberstein@gmail.com> wrote:
>> Procedures with OUT parameters don't return a specific oid: they
>> return a generic "record" oid (2249).
>
>
> Sorry to bother you once more: is it possible to cast any PG RECORD
> (anything OID 2249) into Python _lists_?

Postgres doesn't tell you the oids of the components, so you cannot
cast them to python types: you can only have them as strings.

-- Daniele


Re: flexi adaption/casting scheme

From
Tobias Oberstein
Date:
Am 22.09.2012 20:42, schrieb Daniele Varrazzo:
> On Sat, Sep 22, 2012 at 7:02 PM, Tobias Oberstein
> <tobias.oberstein@gmail.com> wrote:
>>> Procedures with OUT parameters don't return a specific oid: they
>>> return a generic "record" oid (2249).
>>
>>
>> Sorry to bother you once more: is it possible to cast any PG RECORD
>> (anything OID 2249) into Python _lists_?
>
> Postgres doesn't tell you the oids of the components, so you cannot
> cast them to python types: you can only have them as strings.
>
> -- Daniele
>

Ok;( Thinking more about it, I now can see how the PG SQL "wire format"
just isn't self-describing, and that records are really anonymous
composite types.

Anyway, and FWIW, I've implemented a RecordCaster that does like you say
(parses RECORDs into Python lists of strings):

https://github.com/oberstet/scratchbox/blob/master/python/psycopg2/test9.py

Tokenizer code is stolen from your CompositeCaster;)

The stuff is of course of limited use .. all elements get cast to string.

The consequence is, that I probably need to require users of the
JSON-RPC to PG stored procedure stuff to have their (remoted) SPs
without OUT or INOUT parameters and without RECORD types. And probably
without VARIADIC also. Ok, having only 1 OUT works, but then one can use
RETURN just as well.

Thanks!

Tobias