2016-11-10 18:56 GMT+01:00 Tom Lane <t...@sss.pgh.pa.us>: > Pavel Stehule <pavel.steh...@gmail.com> writes: > > 2016-11-09 22:47 GMT+01:00 Tom Lane <t...@sss.pgh.pa.us>: > >> * I really dislike the notion of keying the behavior to a special type > of > >> psql variable. > > > 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. > > Personally, I'd rather have filename tab completion after ":<", and forget > \setfileref --- I do not think that setting a variable first is going to > be the primary use-case for this. In fact, I could happily dispense with > the variable case entirely, except that sometimes people will want to > reference file names that don't syntactically fit into the colon syntax > (eg, names with spaces in them). Even that seems surmountable, if people > are okay with requiring the '<' trailer --- I don't think we need to worry > too much about supporting file names with '<' in them. (Likewise for > '>', if you feel like :<filename> is a less ugly syntax.) >
I found early stop - there are not easy implement any complex syntax in lexer, without complex backup rules. Now, I am coming with little bit different idea, different syntax. :xxxx means insert some content based on psql variable We can introduce psql content commands that produces some content. The syntax can be :{cmd parameters} Some commands: * r - read * rq - read and quote, it can has a alias "<" * rbq - read binary and quote Other commands can be introduced in future Usage: INSERT INTO foo(image) VALUES(:{rbq ~/avatar.jpg}) INSERT INTO foo(xmdoc) VALUES(:{rq "~/current doc.xml"}) INSERT INTO foo(longtext) VALUES(:{< ~/book.txt}) It is general with possible simple implementation - doesn't big impact on psql lexer complexity. What do you think about it? Regards Pavel p.s. the colon is not necessary - we can use {} as special case of ``. INSERT INTO foo(image) VALUES({rbq ~/avatar.jpg}) INSERT INTO foo(xmdoc) VALUES({rq "~/current doc.xml"}) INSERT INTO foo(longtext) VALUES({< ~/book.txt}) Then we can support psql variables there \set avatar ~/avatar.jpg INSERT INTO foo(image) VALUES({rbq :avatar}) > > > 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 > > Seems a bit arbitrary, and not readily extensible to more datatypes. > > I guess an advantage of the parameterized-query approach is that we > wouldn't really have to distinguish different datatypes in the syntax, > because we could use the result of Describe Statement to figure out what > to do. Maybe that outweighs the concern about magic behavioral changes. > > On the other hand, it's also arguable that trying to cater for automatic > handling of raw files as bytea literals is overcomplicating the feature > in support of a very infrequent use-case, and giving up some other > infrequent use-cases to get that. An example is that if we insist on > doing this through parameterized queries, it will not work for inserting > literals into utility commands. I don't know which of these things is > more important to have. > > regards, tom lane >