postgresql - 将 xml 调试到 postgres，一列 myxml 不存在？

Question

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;

在最后一行 myxml 给出了列不存在的错误。

Answer 1

在声明变量 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;

另外 - 如果你只是在每次执行时创建一个表 - 第二次执行会给你错误，那个表存在

你可以使用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