sql - 在 psql 中执行 .sql 文件,从 Excel 单元格值传递参数
问题描述
我正在尝试在 psql 中执行一个 .sql 文件(通过 .bat 脚本),将test.xlsm工作簿的单元格B1和B2中包含的值作为参数传递。我一直在寻找可能的解决方案,但我不明白如何在 .sql 文件中引用这些值(Excel 中的单元格 B1 和 B2)。
这是我迄今为止一直在做的事情。
download_from_postgresql.bat文件包含以下脚本,以便建立连接并通过 psql.exe 运行上述 download_view.sql 文件:
@echo off
setlocal
set PGPASSWORD=mypassword
"D:\PostgreSQL\10\bin\psql.exe" -h myserver.com -U myuser -d mydb -f C:/QA_nepal/download_view.sql
endlocal
download_view.sql文件包含以下脚本,以便导出直接在我连接到的 PostgreSQL 服务器上创建的特定视图的 .csv 文件:
\copy (SELECT * FROM public_qa_stfc_india."join_tables_date_filter=custom_user_filter=none") to 'C:\\QA_nepal\\test.csv' with CSV HEADER;
\q
我所指的join_tables_date_filter=custom_user_filter=none视图是直接在我的 PostgreSQL 服务器上创建的,如下所示:
SELECT * FROM public_qa_stfc_india.step_3_joined_tables
WHERE "left"(step_3_joined_tables._0_general_start, 10) >= '2018-05-03'::text
AND "left"(step_3_joined_tables._0_general_start, 10) <= '2018-05-04'::text;
我想做的是编辑上面包含的视图定义,以便获得类似:
SELECT * FROM public_qa_stfc_india.step_3_joined_tables
WHERE "left"(step_3_joined_tables._0_general_start, 10) >= 'date_par_1'::text
AND "left"(step_3_joined_tables._0_general_start, 10) <= 'date_par_2'::text;
并将单元格 B1 的值传递给 date_par_1,将 B2 的值传递给 date_par_2。我指的 Excel 工作簿位于 C:\QA_nepal\excel\test.xlsm 中。
在这一点上,我不确定如何进行。我应该指示 psql 从 Excel 中读取这些值吗?那么在 download_from_postgresql.bat 中添加指令?或者我应该以某种方式直接在 download_view.sql 文件中引用 Excel 中的值?
在此先感谢您的帮助,斯特凡诺
更新 2018/06/19
我设法在 VB 中编写了一个宏,能够将单元格 B1 和 B2 中的值(值分别为 2018-01-01 和 2015-02-07)传递给 .bat 文件:
Sub batch()
Dim wsh As Object
Dim data_1 As String
Dim data_2 As String
Set wsh = VBA.CreateObject("WScript.Shell")
Dim waitOnReturn As Boolean: waitOnReturn = True
Dim windowStyle As Integer: windowStyle = 1
data_1 = ThisWorkbook.Sheets("Foglio1").Range("B1").Value
data_2 = ThisWorkbook.Sheets("Foglio1").Range("B2").Value
wsh.Run "C:\QA_nepal\download_from_postgresql.bat " & data_1 & " " & data_2, windowStyle, waitOnReturn
End Sub
现在我的download_from_postgresql.bat文件如下所示:
@echo off
SET data_1=%1%
SET data_2=%2%
ECHO %data_1% 'test if the value in B1 is passed to the .bat file correctly
ECHO %data_2% 'test if the value in B2 is passed to the .bat file correctly
setlocal
set PGPASSWORD=mypassword
"D:\PostgreSQL\10\bin\psql.exe" -h myserver.com -U myuser -d mydb -v v1=%data_1% -v v2=%data_2% -f C:/QA_nepal/download_view.sql
endlocal
此时,我不确定如何引用download_view.sql中的变量v1和v2:
\copy (SELECT * FROM public_qa_stfc_india."join_tables_date_filter=custom_user_filter=none") to 'C:\\QA_nepal\\test.csv' with CSV HEADER;
\q
考虑到我的join_tables_date_filter=custom_user_filter=none视图如下所示:
SELECT * FROM public_qa_stfc_india.step_3_joined_tables
WHERE "left"(step_3_joined_tables._0_general_start, 10) >= '2018-05-03'::text
AND "left"(step_3_joined_tables._0_general_start, 10) <= '2018-05-04'::text;
我知道我必须用 2 个变量(比如说 var1 和 var2)替换上面视图中的日期('2018-05-03' 和 '2018-05-04'),然后我必须“连接”这些变量(var1和var2)与.bat文件(v1和v2)中的变量我尝试了不同的方式,例如有类似的东西:
\copy (SELECT * FROM public_qa_stfc_india."join_tables_date_filter=custom_user_filter=none" WHERE var1 = (:v1) AND var2 = (:v2)) to 'C:\\QA_nepal\\test.csv' with CSV HEADER;
\q
和:
SELECT * FROM public_qa_stfc_india.step_3_joined_tables
WHERE "left"(step_3_joined_tables._0_general_start, 10) >= '$var1'::text
AND "left"(step_3_joined_tables._0_general_start, 10) <= '$var2'::text;
但没有成功。一般来说,我实际上对 SQL 还是很陌生,所以我可能走错了路。有什么建议么?
在此先感谢,斯特凡诺
解决方案
考虑使用接收参数并以字符串格式COPY
运行命令的函数替换您存储的硬编码日期且不接收参数的视图。然后调用该函数。psql
CREATE OR REPLACE FUNCTION my_date_filter_func(start_dt TEXT, end_dt TEXT, csv_file TEXT)
RETURNS void AS
$func$
BEGIN
EXECUTE format('COPY (SELECT * FROM public_qa_stfc_india.step_3_joined_tables s
WHERE LEFT(s._0_general_start, 10) >= %L
AND LEFT(s._0_general_start, 10) <= %L) TO %L with CSV HEADER;',
start_dt, end_dt, csv_file);
END
$func$ LANGUAGE plpgsql;
通过VBA shell 调用的 psql命令
...
path = "C:/QA_nepal/test.csv"
cmd_str = "psql ... -c ""SELECT my_date_filter_func('" & data_1 & "', '" & data_2 & "', '" & path & "')"""
wsh.Run cmd_str, windowStyle, waitOnReturn
最后,不清楚为什么将日期字段视为文本,LEFT()
因为您可以直接在WHERE
子句中使用日期文字,并为午夜隐式定义时间戳:00:00:00
。
SELECT *
FROM public_qa_stfc_india.step_3_joined_tables s
WHERE s._0_general_start >= '2018-05-03'
AND s._0_general_start <= '2018-05-04';
推荐阅读
- .net - .NET 5 SDK 可以用来编译 .NET Core 3.1 项目吗?
- spring - 如何使用 HQL 从 2 个表中获取数据,它们之间存在一对多关联?
- adobe - 用于可填充 Adobe Stamp 的 JavaScript
- api - 在 Pentaho 上尝试 Rest Client 但它没有
- reactjs - 解决了 React,将对象作为参数传递给页面,不在 html 中显示,但会在控制台中
- rake - 从 IMAP、Redmine OpenProject 接收电子邮件
- powershell - 活动监视器总数
- apache-spark - 在本地模式下,Spark 上的 CPU 使用率非常低且执行速度慢
- c++ - 模板化的 constexpr 变量
- python - 如何在一张图中绘制多个时间线?