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

On Thu, May 26, 2016 at 4:54 PM, David G. Johnston <
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>
> 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.