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.
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.
I don't see a reason why it should not be possible - the current code can't do it, but with 20 lines more, it should be possible
There is one disadvantage against copy - the content should be fully loaded to memory, but any other functionality should be same.
