Re: proposal: psql \setfileref

2016-11-15 17:39 GMT+01:00 Daniel Verite <daniel(at)manitou-mail(dot)org>:

> Corey Huinker wrote:
>
> > I am not asking for this feature now, but do you see any barriers to
> later
> > adding x/xq/xb/xbq equivalents for executing a shell command?
>
> 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).

Please, try to test my last patch.

Regards

Pavel

>
>
> Best regards,
> --
> Daniel Vérité
> PostgreSQL-powered mailer: http://www.manitou-mail.org
> Twitter: @DanielVerite
>