How can I use Postgres hstore with Ecto?

[Roman Smirnov]

Hi.

Looks like postgrex has a hstore support, but how it could be used in Ecto schema definition?

I've tried 

schema "table" do

  field :settings, :binary

end

But I'm still getting an error "no extension found for oid `21066`"

Env: Elixir 1.0.4, Ecto 0.12.0-rc, Postgrex 0.8.4

[José Valim]

Ecto doesn't know about the hstore type. So you need to create a custom Ecto type that is able to handle them. Maybe this blog post for JSON can be useful:

http://gabrieljaldon.com/articles/using-json-type-in-ecto.html

José Valim

www.plataformatec.com.br

Skype: jv.ptec

Founder and Director of R&D

--
You received this message because you are subscribed to the Google Groups "elixir-ecto" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elixir-ecto...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/fbb525d7-4974-4986-81da-7427d977ecaa%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Roman Smirnov]

Thank you, José.

It was helpful. I figured out how to make hstore work and wrote it in the gist: https://gist.github.com/romul/7fd48cb85d77819511f2

Maybe it will be helpful for other people too.

But I'm curious, why postgrex use patterns like %TypeInfo{send: "hstore_send"} instead of %TypeInfo{type: "hstore"} in the Postgrex.Extensions.Binary?

Is it a bug and pull request is welcome, or is there some intension to limit the applicability of these functions?

For example, in my case I have hstore inside PG schema, and it leads to TypeInfo#send == "schema_name.hstore_send", so I have to manually change TypeInfo#send to call proper function: https://gist.github.com/romul/7fd48cb85d77819511f2#file-hstore_extension-ex-L12-L18

суббота, 27 июня 2015 г., 17:43:41 UTC+3 пользователь José Valim написал:

[Dmitry Aleksandrov]

There exists one ecto plugin for hstore: https://github.com/stavro/ecto_hstore
Check it, may be it is what you are searching for.

[Eric Meadows-Jönsson]

Postgrex supports hstore and we have tests for it for Postgres 9.0+ on travis CI. We match against the send function because it is usually more generic, for example we can generically match on array, range and composite types instead of matching on the individual type names.

Could you please reply with the result of `SELECT * FROM pg_type WHERE oid = 21066`, your postgres version and operating system. I would be interested to know why it doesn't work for you but it works on my OSX machine and on travis CI.

To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/0cc523b4-c4c8-447b-9b3c-1c4e2188cace%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

Eric Meadows-Jönsson

[José Valim]

Eric, to be clear, Roman doesn't need the Postgrex extension because Postgrex already handles it. He needs only the Ecto one, is this correct?

José Valim

www.plataformatec.com.br

Skype: jv.ptec

Founder and Director of R&D

To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/CAM_eaphRD9ZM0c5rqVFVuXbH5m7_HVyzT8cWWRX9B1oEMSobdg%40mail.gmail.com.

[Roman Smirnov]

Hi, Eric.

Take a look at my message with solution above.

There were 2 issues for me:

1) No Ecto support for hstore, solution: https://gist.github.com/romul/7fd48cb85d77819511f2#file-hstore-ex

2) hstore support in Postrex has an issue in case when you work with PG schemas (besides public). 

Here is how type_info for oid = 21066 looks like in my case:

%Postgrex.TypeInfo{array_elem: 0, base_type: 0, comp_elems: [], input: "schema_name.hstore_in", oid: 21066, output: "schema_name.hstore_out", receive: "schema_name.hstore_recv", send: "schema_name.hstore_send", type: "hstore"}

So, I have to use the following workaround https://gist.github.com/romul/7fd48cb85d77819511f2#file-hstore_extension-ex-L12-L18,

b/c https://github.com/ericmj/postgrex/blob/master/lib/postgrex/extensions/binary.ex#L126 would never match without it.

Because of this I propose to use %TypeInfo{type: "hstore"} in ecode/decode for hstore in Postgrex.

воскресенье, 28 июня 2015 г., 11:55:55 UTC+3 пользователь Eric Meadows-Jönsson написал:

[Eric Meadows-Jönsson]

As I mentioned earlier I want to avoid matching on type name because the name is less generic. That hstore doesn't work in your case is indicative of a deeper issue: postgrex should parse out schema names from the function name. Changing the hstore type will only fix it for hstore but the issue still exists for all other types.

To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/fbb1f737-e5a0-4e11-bfb5-c889cbef3911%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

Eric Meadows-Jönsson

[Roman Smirnov]

Honestly, I don't see why send name is more general than type name in case of hstore. If anything, type name looks much more general in this particular case. 

Improving schemas support is definitely worth to do, but I believe that this issue is actual only for types from pg_available_extensions table.

воскресенье, 28 июня 2015 г., 13:48:12 UTC+3 пользователь Eric Meadows-Jönsson написал: