Hi 2016-11-09 22:47 GMT+01:00 Tom Lane <t...@sss.pgh.pa.us>:
> Pavel Stehule <pavel.steh...@gmail.com> writes: > > [ psql-setfileref-2016-10-11.patch ] > > I haven't been paying any attention to this thread, but I got around to > looking at it finally. I follow the idea of wanting to be able to shove > the contents of a file into a query literal, but there isn't much else > that I like about this patch. In particular: > > * I really dislike the notion of keying the behavior to a special type of > psql variable. psql variables are untyped at the moment, and if we were > going to introduce typing, this wouldn't be what I'd want to use it for. > I really don't want to introduce typing and then invent one-off, > unextensible syntax like '^' prefixes to denote what type a variable is. > still I am thinking so some differencing can be nice, because we can use psql file path tab autocomplete. Maybe \setfileref can stay - it will set any variable, but the autocomplete will be based on file path. > > Aside from being conceptually a mess, I don't even find it particularly > convenient. In the shell, if you want to source from a file, you write > "<filename". You aren't compelled to assign the filename to a variable > and then write "<$filename" ... although you can if that's actually > helpful. > > Going by the notion of driving it off syntax not variable type, I'd > suggest that we extend the colon-variablename syntax to indicate > desire to read a file. :<filename< is one pretty obvious idea. > Maybe we could use :<:variablename< to indicate substituting the > content of a variable as the file name to read. > I used the concept of file references because I would not to invent new syntax of psql variables evaluation. If we introduce new syntax, then the variables are not necessary. The syntax :some op has sense, and be used and enhanced in future. What do you thing about following example? INSERT INTO tab VALUES(1, :<varname); -- insert text value -- used text escaping INSERT INTO tab VALUES(1, :<#varname); -- insert bytea value -- used bytea escaping > > * I'm a bit queasy about the idea of automatically switching over to > parameterized queries when we have one of these things in the query. > I'm afraid that that will have user-visible consequences, so I would > rather that psql not do that behind the user's back. Plus, that assumes > a fact not in evidence, namely that you only ever want to insert data > and not code this way. (If \i were more flexible, that objection would > be moot, but you can't source just part of a query from \i AFAIK.) > There might be something to be said for a psql setting that controls > whether to handle colon-insertions this way, and make it apply to > the existing :'var' syntax as well as the filename syntax. > I understand to this objection - The my motivation for parametrized queries was better (user friendly) reaction on syntax errors. In this case the content can be big, the query can be big. When we use parametrized queries, then the error message can be short and readable. Another advantage of parametrized queries is possibility to set parameter type. It is important for binary content. And last advantage is a possibility to use binary passing - it is interesting for XML - it allows automatic encoding conversions. These features are nice, but are not necessary for this patch. > > * I find the subthread about attaching this to COPY to be pretty much of > a red herring. What would that do that you can't do today with \copy? > The primary task is simple - import big XML, JSON document or some binary data to database. This can be partially solved by ref variables, but COPY has more verbose and more natural syntax - the file path autocomplete can be used. \COPY table(column) FROM file FLAG; Second task is not too complex too - export binary data from Postgres and store these data in binary files. Now I have to use final transformation on client side. Third task - one interesting feature of XML type (automatic encoding conversion) is available only with binary input output functions. I would to find a way how this functionality can be accessed without "hard" programming. \COPY (SELECT xmldoc FROM xxx WHERE id = 111) TO file BINARY ENCODING latin1; Regards Pavel > regards, tom lane >
