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

On Tuesday, May 31, 2016, Michael Moore <michaeljmoore(at)gmail(dot)com> wrote:

>
>
> On Thu, May 26, 2016 at 4:54 PM, David G. Johnston <
> david(dot)g(dot)johnston(at)gmail(dot)com
> <javascript:_e(%7B%7D,'cvml','david(dot)g(dot)johnston(at)gmail(dot)com');>> wrote:
>
>> On Thu, May 26, 2016 at 6:59 PM, Michael Moore <michaeljmoore(at)gmail(dot)com
>> <javascript:_e(%7B%7D,'cvml','michaeljmoore(at)gmail(dot)com');>> wrote:
>>
>>>
>>> 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$​
>>>
>>
>> ​That's an artifact of your choice of "$$" for the DO block. If you
>> change the DO block to $do$...$do$ then using $qq$...$$qq$ you would be OK.​
>>
>> 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.
>>>
>>
>> Pretty sure what you are calling cursors are basically prepared
>> statements in PostgreSQL. Or, less explicitly, reused execution plans.
>>
>> ​
>> https://www.postgresql.org/docs/9.5/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
>>
>> """
>> ​Also, there is no plan caching for commands executed via EXECUTE.
>> Instead, the command is always planned each time the statement is run. Thus
>> the command string can be dynamically created within the function to
>> perform actions on different tables and columns.
>> """
>>
>>
>>> 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 ".
>>>
>>
>> ​Basically the way to ​"variable number of bind variables for dynamic
>> SQL" is to not do it and instead use a single bind variable (or none) and
>> decompose the single value into the unknown number of multiple variables
>> that are contained within it.
>>
>> 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.
>>>
>>
>> ​Probably not an option here but something to keep in mind. Particularly
>> useful for large datasets and relatively infrequent execution. You can
>> always ANALYZE your temporary table.
>>
>>
>>> Per my best interpretation of your advice tempered by my understanding
>>> of the application, here is my new approach:
>>>
>>>
>> ​IIUC, this is going to be your next question:
>>
>> ​execute sql into rslt using v_bind_values[1], v_bind_values[2];
>>
>> I would probably target 0 bind parameters and just resign yourself to a
>> fully built up SQL statement.
>>
>> instead of this:
>> sql := SQL ||
>> $z$ and 3333 $z$|| fbind_csv_number(parm_num);
>>
>> something like this:
>> sql := SQL ||
>> $z$ and 3333 $z$|| make_any_bigint_array(parm_num);
>> --> ... and 3333 < $pre$= ANY( ($pre$||
>> string_to_array(cleaninput_embedded_integers_only(<input>),
>> ',')::bigint[]::text || $post$)::bigint[])$post$ >
>>
>> You need to end up with a well-formed string within the ANY but you don't
>> have to create it yourself, like you did originally.
>>
>> David J.
>>
>> David,
> sorry, I'm not quite following this last bit. The part where you said:
>
>>
>> *sql := SQL || *
>> * $z$ and 3333 $z$|| make_any_bigint_array(parm_num);*
>> *--> ... and 3333 < $pre$= ANY( ($pre$||
>> string_to_array(cleaninput_embedded_integers_only(<input>),
>> ',')::bigint[]::text || $post$)::bigint[])$post$ >*
>>
>>
> Why are you passing in the parm_num on "
> *make_any_bigint_array(parm_num)"?*
> I'm guessing you meant to say parm_value, not parm_num. If so, then every
> thing else makes perfect sense.
>
>
I would concur that param_value was intended.

David J.