Prepared statements for PostgreSQL

[sm-shaw]

PostgreSQL functions supports prepared statements https://www.postgresql.org/docs/9.3/sql-prepare.html with testing showing that doing so provides some small percentage performance gains. Similar functionality such as bind variables is already used for other databases:

For example ostat can be prepared once as:

prepare ostat (INTEGER, INTEGER, INTEGER, INTEGER, VARCHAR) AS select * from ostat(\$1,\$2,\$3,\$4,'\$5') as (ol_i_id NUMERIC, ol_supply_w_id NUMERIC, ol_quantity NUMERIC, ol_amount NUMERIC, ol_delivery_d TIMESTAMP, out_os_c_id INTEGER, out_os_c_last VARCHAR, os_c_first VARCHAR, os_c_middle VARCHAR, os_c_balance NUMERIC, os_o_id INTEGER, os_entdate TIMESTAMP, os_o_carrier_id INTEGER)

and repeatedly executed as:

set result [ pg_exec_prepared $lda ostat {} {} $w_id $d_id $c_id $byname $name ]

instead of being parsed each time as follows:

set result [pg_exec $lda "select * from ostat($w_id,$d_id,$c_id,$byname,'$name') as (ol_i_id NUMERIC, ol_supply_w_id NUMERIC, ol_quantity NUMERIC, ol_amount NUMERIC, ol_delivery_d TIMESTAMP, out_os_c_id INTEGER, out_os_c_last VARCHAR, os_c_first VARCHAR, os_c_middle VARCHAR, os_c_balance NUMERIC, os_o_id INTEGER, os_entdate TIMESTAMP, os_o_carrier_id INTEGER)" ]

At the current time prepared statements only support the select used to call functions and therefore PostgreSQL prepared statements do not support stored procedures as they do not support the call command. This issue also to serve as a placeholder if stored procedures become supported to add the same prepared statement functionality.

prepare slev (INTEGER, INTEGER, INTEGER, INTEGER) AS call slev($1,$2,$3, $4);
     ;
     );
ERROR:  syntax error at or near "call"
LINE 1: prepare slev (INTEGER, INTEGER, INTEGER) AS call slev(
                                                 ^

[sm-shaw]

Closed per pull request PostgreSQL prepared statements for functions and tablespace option for schema builds #95