Maciek Sakrejda <msakrejda@truviso.com> writes:
> I have a UDT:
> CREATE TYPE foo AS (a integer, b integer, c hstore);
> I need to construct the literal representation of an array of these
> for input to a textual COPY. The hstore escaping rules are fairly
> clear (I can quote all keys and values with double quotes, and escape
> double quotes in keys and values with a backslash (two backslahses,
> actually, due to standard_conforming_strings being off). Putting this
> into my UDT literal is also fairly clear (I quote the full hstore
> literal with double quotes, I double the double quotes around hstore
> keys and values, and add four more backslashes (one for the hstore
> literal, one for the UDT literal, and double that for
> standard_conforming_strings). So each quote in an hstore key or value
> is preceded by six backslashes (and each backslash is preceded by
> seven). However, this is where I get lost.
On the whole I think you'd be best off doing that in three passes.
First, run the appropriate escaping rules on each hstore key and
value string, and actually assemble a valid input string for hstore.
Then, run the value escaping rules for records on that string, and
assemble it together with the other field values into a valid input
string for a record type. Finally, run the array escaping rules
on that string, and assemble it together with others into a valid
array input string.
The record and array escaping rules are here:
http://www.postgresql.org/docs/9.0/static/rowtypes.html#AEN7091
http://www.postgresql.org/docs/9.0/static/arrays.html#ARRAYS-IO
and I think the hstore rules are spelled out in the docs for that
contrib module.
> I tried following both the
> array docs and trying to reverse engineer the behavior by using the
> ARRAY[...] syntax, but I can't figure out the logic.
Neither record nor hstore quote exactly like arrays, so that's probably
part of your problem. (No doubt it'd be better if they did, but we've
got too much historical baggage :-()
regards, tom lane