Paul Norman
2013-06-29 20:05:15 UTC
I am looking for a way to access hstore fields from pqxx.
My scenario is I have what is essentially a three column table
CREATE TABLE nodes (
id bigint primary key,
geom geometry, -- postgis geometry
tags hstore);
I want to run a query, selecting the rows on the basis of stuff. Right now
I'm running the query SELECT id, geom FROM nodes WHERE geom && ...; and then
iterating over the rows and doing SELECT (each(tags)).key as k,
(each(tags)).value as v from nodes where id=$1;. Although this works I would
much prefer to not have to run an additional query for each row, but I
cannot figure out how to handle a hstore with pqxx. How do I do so?
If there is no way to handle a hstore, it occurred to me to use
hstore_to_matrix() or hstore_to_array() to turn the hstore into a
2-dimensional or 1-dimensional array, but I couldn't figure out how to
handle an array field in pqxx. How do I do so?
My scenario is I have what is essentially a three column table
CREATE TABLE nodes (
id bigint primary key,
geom geometry, -- postgis geometry
tags hstore);
I want to run a query, selecting the rows on the basis of stuff. Right now
I'm running the query SELECT id, geom FROM nodes WHERE geom && ...; and then
iterating over the rows and doing SELECT (each(tags)).key as k,
(each(tags)).value as v from nodes where id=$1;. Although this works I would
much prefer to not have to run an additional query for each row, but I
cannot figure out how to handle a hstore with pqxx. How do I do so?
If there is no way to handle a hstore, it occurred to me to use
hstore_to_matrix() or hstore_to_array() to turn the hstore into a
2-dimensional or 1-dimensional array, but I couldn't figure out how to
handle an array field in pqxx. How do I do so?