Re: proposal: psql \setfileref

On Wed, Aug 31, 2016 at 11:32 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

Hi

I propose a new type of psql variables - file references. The content of file reference is specified by referenced file. It allows simple inserting large data without necessity of manual escaping or using LO api.

When I wrote the patch, I used parametrized queries for these data instead escaped strings - the code is not much bigger, and the error messages are much more friendly if query is not bloated by bigger content. The text mode is used only - when escaping is not required, then content is implicitly transformed to bytea. By default the content of file is bytea. When use requires escaping, then he enforces text escaping - because it has sense only for text type.

postgres=# \setfileref a ~/test2.xml
postgres=# \setfileref b ~/avatar.gif
postgres=# insert into test values(convert_from(:a, 'latin2')::xml, :b); -- xml is passed as bytea
postgres=# insert into test values(:'a', :b); -- xml is passed via unknown text value

The content of file reference variables is not persistent in memory.

Comments, notes?

Regards

Pavel

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Clearly jumping ahead on this one, but if the fileref is essentially a pipe to "cat /path/to/file.name", is there anything stopping us from setting pipes?
 

My interest is primarily in ways that COPY could use this.