The difficulty with prepared statements is that you need
to provide types when you prepare. We could declare them
all TEXT, but I am not entirely sure if when you do that
whether the resulting query plan is any good (when you give
explicit text strings as we do in our fake bind vars it might be smarter than it would be w/o knowing anything).
prepared statements are per db session but for us you would
not want to prepare everything since the session would get
pretty big, also things like dynamic sort statements and
restrictions like you get with listbuilder couldn't be prepared (well, they could but you would need to generate
a synthetic key like a hash to identify them rather than using the statement name).
Interestingly, I think you could do it all withough any changes to the db driver itself. Inside db_exec
you would take a statement like dbqd.acs-tcl.tcl.file.func.stmnt
select ... from foo, goo, ... where foo.x = :val and goo.y = :val2 ...
change it to first do a
PREPARE dbqd.acs-tcl.tcl.file.func.stmnt (TEXT,TEXT) AS
select ... from foo, goo, ... where foo.x = $1 and goo.y = $2 ...
and have a nsv or something to save the that that statement was prepared on that pool
(since PREPARES are per backend) and save the execute statement (which would be static). Then for that and all subsequent calls for that pool you would issue:
EXECUTE dbqd.acs-tcl.tcl.file.func.stmnt (:val, :val2)
to actually retrieve data.
The problem with a blanket system like this is that if you do ddl, the prepared statement
does not pick it up. eg:
xarg=# create table tmp(x integer);
CREATE TABLE
xarg=# insert into tmp values(1);
INSERT 254417 1
xarg=# prepare xp as select * from tmp;
PREPARE
xarg=# execute xp;
x
---
1
(1 row)
xarg=# alter table tmp add column y integer;
ALTER TABLE
xarg=# execute xp;
x
---
1
(1 row)
Also, if you watch files and the query changes you would need to
flush prepared queries for those that change (well, probably just
mark them dirty and then flush if used).
