Search code examples
rubypostgresqlhstore

escaping Postgresql hstore type with ruby?

I'm using postgresql type hstore with rails (pg gem). What I need is mass-insert hstore values in lots of rows, so I want to make SQL string.

But I cannot find hstore quoting method in pg gem (I thought there should be one).

Am I missing something, or I should write this quotation method by myself?

Solution

  • I'd probably use the hstore(text, text) and hstore(text[], text[]) functions instead of trying to build the string versions. Combine those with the array constructor syntax and you'd only be dealing with string literals. For example, if your hstore is a single key and value, then:

    insert into your_table (hstore_column)
values (hstore('key', 'value'))

    will work and give you "key"=>"value" in hstore_column; if your hstore has multiple pairs then:

    insert into your_table (hstore_column)
values (hstore(array['key1', 'key2', 'key3'], array['value1, 'value2', 'value3']))

    will give you "key1"=>"value1","key2"=>"value2","key3"=>"value3" in hstore_column.

    I doubt the overhead of calling the functions will be much different than the overhead of parsing the string versions.