postgresql function query execute using parameter for ANY clause - get error - query string argument of EXECUTE is null

Question

PostgreSQL 11.4, compiled by Visual C++ build 1914, 64-bit

Reviewed dozens of articles in Stackoverflow, no real match. The need: pass a comma separated string (id values), and use that list with the "ANY" postgresql clause.

Code

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;

I have checked p_facilities to ensure it is not empty or null. I have even specifically set p_facilities to a value inside the function like this:

p_facilities text = '3';

The returned error is consistently: 'query string argument of EXECUTE is null (SQL State 22004)'

Answer 1

The problem is that you are not referring to the using parameter anywhere in your query. Instead, you are concatenating $1 directly into your query, and this $1 refers to the first argument of the pl/pgsql function you are in (and apparently is NULL).

To use parameters in dynamically executed sql and pass them through using, you need to hardcode the text $1 into the query string:

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

To interpolate a string into the query, you don't need any using clause, just refer to the string directly:

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

However, notice that you don't need (and shouldn't use) dynamic sql here at all. You're constructing a value, not sql structure. You can just refer to that directly in a normal query:

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