CODEWITHSUNDEEP
postgresqlplpgsql
James
James

Reputation: 97

Debug xml to postgres, a column myxml not exist?

CREATE OR REPLACE FUNCTION f_sync_from_xml()
  RETURNS boolean AS
$BODY$
DECLARE
    myxml    xml;
    datafile text := 'Questo PC/Documenti/ABBATE_EMANUELE_Lvl1F2Va_20160418-1759.xml';
BEGIN

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

END;
$BODY$ language plpgsql;


CREATE TEMP TABLE tmp AS
SELECT (xpath('//some_id/text()', x))[1]::text AS id
FROM   unnest(xpath('/xml/path/to/datum', myxml)) x;

At the last line myxml gives an error the column not exists.

Upvotes: 0

Views: 217

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51609

put statement inside the function body where you declare the variable myxml

CREATE OR REPLACE FUNCTION f_sync_from_xml()
  RETURNS boolean AS
$BODY$
DECLARE
    myxml    xml;
    datafile text := 'Questo PC/Documenti/ABBATE_EMANUELE_Lvl1F2Va_20160418-1759.xml';
BEGIN

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

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

also - if you jsut create a table on each execution - second execution will give you error, that tble exists

you can use IF NOT EXISTS:

t=# create temp table if not exists tmp as select now();
SELECT 1
Time: 57.280 ms
t=# create temp table if not exists tmp as select now();
NOTICE:  relation "tmp" already exists, skipping
CREATE TABLE AS
Time: 0.223 ms

Upvotes: 2

Related Questions