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)'

Why are you using dynamic SQL at all? Just return (select … where xx.facility_id = any(string_to_array(p_facilities, ',')));. — Bergi
– Bergi, Commented Sep 26, 2020 at 14:39

Bergi · Accepted Answer · 2020-09-26 14:48:10Z

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[] );

answered Sep 26, 2020 at 14:48

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Charlie Over a year ago

Thanks. I'm always a little concerned with SQL injection when using strings so I went the wrong way with this. I see the string_to_array() should protect against any SQL injection. There are other queries where I have several parameters that I use in the query (e.g. this = $1 and that = $2...). But I think you showed me how I can combine all these together.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related