Re: My "variable number of bind variables for dynamic SQL" solution. Comments?

I tried the $ quoting and was not able to get it to work because I am trying to also concatenate in the number portion of the bind variable ...

bind_number = 1;

​​sql := $qq$

SELECT vendor_name FROM tx_vendor WHERE active_flag = 'Y'

AND vendor_key = $$qq$||bind_number::text||$qq$

AND code2tcode = 123

$qq$​

You point about "two better-named functions" is well taken. I will do as you suggest.

Next point. I have concerns about using EXECUTE FORMAT. Specifically about the number of cursors that would be generated as a result.  Would using a literal cause more cursors than using a bind variable?

1) select vendor_name from tVendor where credit = 'CAT'; -- cursor 1

2) select vendor_name from tVendor where credit = 'DOG';  -- cursor 2

3) select vendor_name from tVendor where credit = $1; -- can be any value, reuses the same cursor

Perhaps Postgres is smart enough to reuse the same cursor for 1 and 2 above? In the real world, my function will be called thousands of times per minute, so performance is critical. 

Next

In reference to the part where you said:

​The usual solution to $subject is to either use something like " value = ANY(array) " or populate a temporary table and write your SQL to join against that temporary table.​  You incorporate ANY into your query but you are trying to populate it dynamically.  The construction of the array should be from a serialized input:

I'm confused about what you meant by  "solution to $subject ".

As for the "temporary table" approach, I have a concern that joining additional tables might create a less than optimal execution plans especially given that there are no stats for these temporary tables. I have no idea if this concern is valid.

Per my best interpretation of your advice tempered by my understanding of the application, here is my new approach: