Postgresql – SQL to read XML from file into PostgreSQL database

postgresqlxml

How can I write SQL to read an XML file into a PostgreSQL XML value?

PostgreSQL has a native XML data type with the XMLPARSE function to parse a text string to that type. It also has ways to read data from the filesystem; the COPY statement, among others.

But I don't see a way to write native PostgreSQL SQL statements to read the content from a filesystem entry and use that to populate an XML value. How can I do this?

Best Answer

Similar to this answer to a previous question, and if you don't want the restrictions of pg_read_file() (in short: pg_read_file can't read files outside the database directory, and reads text in the current session's character encoding).

This function works for any path, but needs to be created as superuser:

create or replace function stack.bytea_import(p_path text, p_result out bytea) 
                   language plpgsql as $$
declare
  l_oid oid;
begin
  select lo_import(p_path) into l_oid;
  select lo_get(l_oid) INTO p_result;
  perform lo_unlink(l_oid);
end;$$;

lo_get was introduced in 9.4 so for older versions you would need:

create or replace function stack.bytea_import(p_path text, p_result out bytea) 
                   language plpgsql as $$
declare
  l_oid oid;
  r record;
begin
  p_result := '';
  select lo_import(p_path) into l_oid;
  for r in ( select data 
             from pg_largeobject 
             where loid = l_oid 
             order by pageno ) loop
    p_result = p_result || r.data;
  end loop;
  perform lo_unlink(l_oid);
end;$$;

then:

select convert_from(stack.bytea_import('/tmp/test.xml'), 'utf8')::xml;
Related Question