在PSQL脚本中访问外部XML文件作为变量(从BASH脚本采购)

Question

遵循此示例，我在 *.sql脚本中使用PostgreSQL变量遇到了麻烦：

• 我想使用bash脚本

在许多XML数据文件上进行迭代

• BASH脚本将XML文件名分配给一个变量，该文件将传递给SQL脚本

• 由该bash脚本调用的SQL脚本将这些数据加载到Postgresql

中

如果我直接源自XML文件，则没有问题；但是，我无法在我的SQL脚本中访问该变量：

在我的SQL脚本(hmdb.sql(中，我可以访问PSQL变量:bash_var(从bash脚本传递(：

echo 'nEXTERNAL VARIABLE (= "datafile", HERE):' :bash_var 'n'

和/或直接引用XML文件，

datafile text := 'hmdb/hmdb.xml';

，但不是一个变量：

datafile text := 'bash_var';

---

hmdb.sh

#!/bin/bash
DATA_DIR=data/
for file in $DATA_DIR/*.xml
  do
    bash_var=$(echo ${file##*/})
    echo $bash_var
    psql -d hmdb -v bash_var=$bash_var -f hmdb.sql
done

Answer 1

好吧，这是我的解决方案。

我在我的persagen.com博客上发布了一个更详细的答案。

基本上，我决定废除DO $$DECLARE ...方法(SO 49950384中描述(，以支持下面的简化方法。

然后，我能够访问bash/psql共享变量:bash_var，因此：

xpath('//metabolite', XMLPARSE(DOCUMENT convert_from(pg_read_binary_file(:'bash_var'))))

这是一个示例SQL脚本，说明了用法：

hmdb.sql

c hmdb
CREATE TABLE hmdb_identifiers (
  id SERIAL,
  accession VARCHAR(15) NOT NULL,
  name VARCHAR(300) NOT NULL,
  cas_number VARCHAR(12),
  pubchem_cid INT,
  PRIMARY KEY (id),
  UNIQUE (accession)
);
echo 'n[hmdb.sql] bash_var:' :bash_var 'n'
-- UPDATE (2019-05-15): SEE MY COMMENTS BELOW RE: TEMP TABLE!
CREATE TEMP TABLE tmp_table AS 
SELECT 
  (xpath('//accession/text()', x))[1]::text::varchar(15) AS accession
  ,(xpath('//name/text()', x))[1]::text::varchar(300) AS name 
  ,(xpath('//cas_registry_number/text()', x))[1]::text::varchar(12) AS cas_number 
  ,(xpath('//pubchem_compound_id/text()', x))[1]::text::int AS pubchem_cid 
-- FROM unnest(xpath('//metabolite', XMLPARSE(DOCUMENT convert_from(pg_read_binary_file('hmdb/hmdb.xml'), 'UTF8')))) x
FROM unnest(xpath('//metabolite', XMLPARSE(DOCUMENT convert_from(pg_read_binary_file(:'bash_var'), 'UTF8')))) x
;
INSERT INTO hmdb_identifiers (accession, name, cas_number, pubchem_cid)
  SELECT lower(accession), lower(name), lower(cas_number), pubchem_cid FROM tmp_table;
DROP TABLE tmp_table;

---

SQL脚本注意：

• 在xpath语句中我根据postgres表格架上重铸 ::text(例如： ::text::varchar(15)(。

• 更重要的是，如果我做过不是在XPATH语句中重新销售了数据类型，而字段条目(例如name长度(超过了SQL varchar(300)长度限制，则这些数据列出了PSQL错误，并且该表未更新(即空置表结果(。

我上传了此答案中使用的XML数据文件

https://gist.github.com/victoriastuart/d1b1959bd31e4de5ed951ff4fe4fe3c3184

直接链接：

• hmdb_metabolites_5000-01.xml

• hmdb_metabolites_5000-02.xml

• hmdb_metabolites_5000-03.xml

• 来源：hmdb.ca

  • 引用

---

更新(2019-05-15(

在后续工作中，在我的研究博客文章中详细介绍了将纯文本导出到Postgresql，我将XML数据直接加载到PostgreSQL中，而不是使用temp表。

tl/dr。在该项目中，我观察到以下改进。

Parameter | Temp Tables  | Direct Import | Reduction
    Time: | 1048 min     | 1.75 min      | 599x
   Space: | 252,000 MB   | 18 MB         | 14,000x