Passing a dict as JSON argument to a custom Postgres function

[Stefan Urbanek]

Hi,

I have a custom PostgreSQL function that takes two JSON-type arguments:

CREATE OR REPLACE FUNCTION "update_json"(original json, update_obj json)

When I try to use the function in sqlalchemy:

a_dictionary = dict(...)

value = sqlalchemy.sql.func.update_json(a_json_column, a_dictionary) 

table.update().values(a_json_column=value)

It fails with:

sqlalchemy.exc.ProgrammingError: (ProgrammingError) can't adapt type 'dict' 

What am I missing?

Thanks,

Stefan 

[Stefan Urbanek]

Update: I added:

from psycopg2.extras import Json

And wrapped my value:

a_dictionary = Json(a_dictionary)

Which moved me forward to:

sqlalchemy.exc.ProgrammingError: (ProgrammingError) function update_json_with_oplog_entry(json, unknown) does not exist

How do I define argument types of a custom function?

[Jonathan Vanasco]

look in sqlalchemy/dialects/postgresql/json.py

it defines the column types and elements for JSON support in postgres

i think you may be able to use the JSON type to accomplish this.

[Jonathan Vanasco]

i think you could also just serialize the JSON ( ie, `json.dumps()`)  and pass that in.   the JSON type just wraps some (de)serialization and handles the comparators.   postgres expects json as strings, not objects.

[Michael Bayer]

i think if you say literal(a_dictionary, type_=postgresql.JSON) that should invoke the proper handling.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.