Re: proposal: psql \setfileref

2016-11-10 18:56 GMT+01:00 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:

> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> > 2016-11-09 22:47 GMT+01:00 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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
>