Re: proposal: psql \setfileref

2016-11-16 16:59 GMT+01:00 Robert Haas <robertmhaas(at)gmail(dot)com>:

> On Tue, Nov 15, 2016 at 11:48 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
> >> For text contents, we already have this (pasted right from the doc):
> >>
> >> testdb=> \set content `cat my_file.txt`
> >> testdb=> INSERT INTO my_table VALUES (:'content');
> >>
> >> Maybe we might look at why it doesn't work for binary string and fix
> that?
> >>
> >> I can think of three reasons:
> >>
> >> - psql use C-style '\0' terminated string implying no nul byte in
> >> variables.
> >> That could potentially be fixed by tweaking the data structures and
> >> accessors.
> >>
> >> - the backtick operator trims ending '\n' from the result of the command
> >> (like the shell), but we could add a variant that doesn't have this
> >> behavior.
> >>
> >> - we don't have "interpolate as binary", an operator that will
> >> essentially apply PQescapeByteaConn rather than PQescapeStringConn.
> >>
> >> For example, I'd suggest this syntax:
> >>
> >> -- slurp a file into a variable, with no translation whatsoever
> >> \set content ``cat my_binary_file``
> >>
> >> -- interpolate as binary (backquotes instead of quotes)
> >> INSERT INTO my_table VALUES(:`content`);
> >>
> >> If we had something like this, would we still need filerefs? I feel like
> >> the point of filerefs is mainly to work around lack of support for
> >> binary in variables, but maybe supporting the latter directly would
> >> be an easier change to accept.
> >
> > I leaved a concept of fileref - see Tom's objection. I know, so I can
> hack
> > ``, but I would not do it. It is used for shell (system) calls, and these
> > functionality can depends on used os. The proposed content commands can
> be
> > extended more, and you are doesn't depends on o.s. Another issue of your
> > proposal is hard support for tab complete (file path).
>
> On the other hand, it seems like you've invented a whole new system of
> escaping and interpolation that is completely disconnected from the
> one we already have. psql is already full of features that nobody
> knows about identified by incomprehensible character combinations.
> Daniel's suggestion would at least be more similar to what already
> exists.
>
>
The Daniel's proposal has important issues:

1. you need to store and hold the content in memory
2. you cannot use tab complete - without it this feature lost a usability
3. you have to do two steps
4. Depends on o.s.

Regards

Pavel

> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>