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

Question

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.

Vao Tsun · Accepted Answer · 2017-11-28 09:23:19Z

2

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

edited Nov 28, 2017 at 9:23

answered Nov 28, 2017 at 8:26

Vao Tsun

52.5k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

James Over a year ago

I tried datafile test := 'ABBATE_EMANUELE_Lvl1F2Va_20160418-1759.xml' and I insert the file xml in the path but I have an error not find the flie

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related