CODEWITHSUNDEEP
postgresqlplpgsql
James
James

Reputation: 97

Absolute path not allowed on row 7, xml to postgres?

CREATE OR REPLACE FUNCTION j_f_sync_from_xml()
RETURNS boolean AS
$BODY$
DECLARE
myxml    xml;
datafile text := 
'C:\Users\Polichetti\Documents\ABBATE_EMANUELE_Lvl1F2Va_20160418-1759.xml';
BEGIN

myxml := pg_read_file(datafile, 0, 100000000); 

CREATE TABLE james AS
SELECT (xpath('//some_id/text()', x))[1]::text AS id
FROM   unnest(xpath('/xml/path/to/datum', myxml)) x;
END;
$BODY$ language plpgsql;
SELECT * from james;

I find an error, absolute path not allowed on row 7. Probably I don't know which path I have to use.

Upvotes: 0

Views: 2073

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51609

https://www.postgresql.org/docs/current/static/functions-admin.html#FUNCTIONS-ADMIN-GENFILE

Only files within the database cluster directory and the log_directory can be accessed. Use a relative path for files in the cluster directory, and a path matching the log_directory configuration setting for log files.

you can access just any OS file, run show data_directory and show log_directory o find out where you can read file with pg_read_file

If you want to load xml to db, I'd rather use different approach, eg:

create table xml(body text);
copy xml from '/absolute/path/to/file.xml';
select string_agg(body,'')::xml from xml;

this is the easiest example. you can look on the web for more, eg using large objects utils

Upvotes: 2

Related Questions