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

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.