Re: COPY enhancements

2009/9/11 Stephen Frost <sfrost(at)snowman(dot)net>:
>>>>        Postgres has a hstore data type which seems well suited for passing
>>>> key/value option pairs...
>>
>> Quite apart from any other reason, it is not builtin, so there is no way
>> that any builtin thing can use it.
>
> Clearly, that's fixable..  I think it's an interesting concept, but I
> don't know that I'd advocate it (using hstore for this in some way).

Unless I'm missing something, which is possible, this whole line of
conversation is based on a misunderstanding. Data types, like hstore,
are things that have input/output functions, index methods, and
on-disk representations. In-memory data structures require none of
these things, and can use techniques not suitable for on-disk
representations, such as pointers. The parser already has an object
called a DefElem which is well-suited for exactly the kind of option
handling we're talking about here, and hstore would not be, not only
because it's the wrong kind of object (a data type rather than an
in-memory data structure), but because a DefElem can do things that
hstore can't, like store as the associated value a list of parse
nodes.

The original reference to hstore was a suggestion that it might be
possible to pass an hstore argument to COPY rather than having to
build up a command string and pass it to EXECUTE. That may or may not
be a useful innovation - personally, I tend to think not - but it
seems to me that it would require COPY to execute an arbitrary
subquery and use the results as options. We have no other commands
that work that way to my knowledge, but beyond that, Pierre Frédéric
Caillaud seemed to be suggesting this would be an "easier" way to
implement an options syntax. Integrating hstore into core and then
making COPY able to execute a subquery to get its options is certainly
not easier than a straightforward grammar modification; it's taking a
small project and turning it into several big ones.

...Robert