This came up as a question on IRC: how to read an XML file on the server into a table column?
One solution, albeit far from ideal, would be:
create or replace function xml_import(filename text) returns xml volatile language plpgsql as $f$ declare content bytea; loid oid; lfd integer; lsize integer; begin loid := lo_import(filename); lfd := lo_open(loid,262144); lsize := lo_lseek(lfd,0,2); perform lo_lseek(lfd,0,0); content := loread(lfd,lsize); perform lo_close(lfd); perform lo_unlink(loid); return xmlparse(document convert_from(content,'UTF8')); end; $f$;
This obviously only works when called as superuser; it’s also not safe to make it security-definer without also coding in a lot of restrictions on the filename. A possible refinement would be to examine the file content to try and determine the actual encoding, rather than hardcoding that as UTF-8.
Changing the return type to
bytea and the last line of the function body to
allows the code to work for binary files instead.