postgresql - plpgsql动态插入表名作为变量和数组变量在where子句中
问题描述
我正在尝试编写一个函数,该函数以表名作为变量运行插入语句,并且 where 子句接受一个数组作为另一个变量。
CREATE OR REPLACE FUNCTION f_dynamic_sql()
RETURNS void
LANGUAGE plpgsql
AS
$body$
DECLARE
rec record;
iterator float4 := 1;
tbl_name text;
BEGIN
DROP TABLE IF EXISTS t_ar; CREATE TEMP TABLE t_ar (reg text, zones text[]);
INSERT INTO t_ar VALUES
('NA', '{"US","UG","UC","UR"}'),
('NE', '{"UK", "SP"}'),
('LA', '{"CA","EC","WC","EC","WC"}');
FOR rec IN SELECT zones from t_ar
LOOP
tbl_name := 'schema.table_' || iterator;
EXECUTE format('INSERT INTO %s
SELECT
DATE_PART(''MONTH'', month)::INT AS month,
SUM(COALESCE(prev_year,0))::INT AS py,
SUM(COALESCE(last_year,0))::INT AS ly
FROM org_table
WHERE load_area IN (SELECT UNNEST(rec.zones))
GROUP BY 1
ORDER BY 1', tbl_name);
iterator := iterator + 1;
END LOOP;
END;
$body$
VOLATILE
COST 100;
这在执行格式之外运行良好,但是我不能将表名作为变量,但在执行格式中它在 SQL 查询中显示语法错误。SQL 查询在外部运行正常。
解决方案
您需要在format()
函数中使用占位符和参数。
使用时的实用提示EXECUTE format()
- 使用RAISE NOTICE '%',
而不是EXECUTE
在 psql 中运行该函数以查看它实际生成的查询。当您确定生成的查询正确时,请替换RAISE NOTICE '%',
为EXECUTE
. 例子:
...
FOR rec IN SELECT reg, zones FROM t_ar
LOOP
RAISE NOTICE '%', format('
INSERT INTO my_schema.table_%s
SELECT
DATE_PART(''MONTH'', month)::INT AS month,
SUM(COALESCE(prev_year,0))::INT AS py,
SUM(COALESCE(last_year,0))::INT AS ly
FROM org_table
WHERE load_area = ANY (%L)
GROUP BY 1
ORDER BY 1', rec.reg, rec.zones);
END LOOP;
...
推荐阅读
- r - 如何从 R 中 Shiny 中的反应式表达式访问变量
- html - 如何使图像显示在文本的正下方,而不是在下一个 div 内容的后面?
- pine-script - 使用多个数组元素为相同的符号生成多行(Pine 脚本)
- macos - Mac OS - `brew list` 以便排除木桶
- databricks - ADLS - 从 Databricks for SQL 模式访问 ADLS
- python - TypeError:“datetime.datetime”对象的描述符“日期”不适用于“int”对象
- vba - 在 27 页文档上运行宏时 Word 内存不足
- java - 如何从 Spring Boot/Java 添加/读取多个数据中心属性
- c# - 如何使用集合编辑器窗口在 GroupBox 中创建文本框?
- angular - 分页器没有以角度读取数据