postgresql函数查询使用ANY子句的参数执行-获取错误-execute的查询字符串参数为null

Question

PostgreSQL 11.4，由Visual C++build 1914编译，64位

在Stackoverflow上查看了数十篇文章，没有真正的匹配。需要：传递一个逗号分隔的字符串(id值(，并将该列表与"一起使用；ANY"；postgresql子句。

代码

return query execute 
'select aa.id, aa.course_id, aa.comments, aa.curr_cont_t_id, aa.date_done, ' || 
'aa.unit_id, aa.time_seq, aa.week_num, bb.module_id, bb.expected_hrs, ' || 
'bb.title unit_title, cc.module_name, cc.tally_hours, cc.time_of_day, ' || 
'bb.file_upload_expected, aa.app_files_id, xx.facility_id ' ||
'from course_content aa ' || 
'left outer join units bb on aa.unit_id = bb.id ' || 
'left outer join module_categories cc on bb.module_id = cc.id ' || 
'left outer join courses xx on aa.course_id = xx.id ' || 
'where xx.facility_id = any(''{' || $1 || '}'') '
using p_facilities;

我已经检查了p_facilities以确保它不是空的或null。我甚至在函数中专门设置了p_facilities的值，如下所示：

p_facilities text = '3';

返回的错误始终为："EXECUTE的查询字符串参数为null(SQL State 22004(">

Answer 1

问题是您没有在查询中的任何位置引用using参数。相反，您将$1直接连接到查询中，而这个$1引用了您所在的pl/pgsql函数的第一个参数(显然是NULL(。

要在动态执行的sql中使用参数并通过using传递它们，需要将文本$1硬编码到查询字符串中：

EXECUTE 'SELECT … WHERE xx.facility_id = any($1)' USING some_array;

要在查询中插入字符串，不需要任何using子句，只需直接引用字符串即可：

EXECUTE 'SELECT … WHERE xx.facility_id = any(''{' || p_facilities || '}'')';

但是，请注意，这里根本不需要(也不应该使用(动态sql。您正在构建一个值，而不是sql结构。您可以在正常查询中直接引用它：

SELECT … WHERE xx.facility_id = any( ('{' || p_facilities || '}')::int[] );
-- or better
SELECT … WHERE xx.facility_id = any( string_to_array(p_facilities, ',')::int[] );