It would be very nice to have a way to get a list of prepared statements. Currently there does not seem to be any:
[1]<br/><br />My problem is this: I am using pgsql with PHP, connecting using pg_pconnect(), which will reuse pgsql
connectionsif possible. This means that the pgsql connection returned to my freshly instantiated PHP instance can have
"predefined"prepared statements. It would be very nice for performance if I were able to reuse those, however there is
noway to know what prepared statements are defined, since there is no pgsql interface to list them. In fact, I am
currentlyprefixing all my prepared query names with a per-session random string to avoid name clashes from previous
sessions.<br/><br />The only way to test if a prepared statement with a given name exists seems to be to try to define
anew one with the same name. If this gives an error then there exist a statement with that name, but I don't think you
cantell whether it is the same actual query as the new one you wanted to define.<br /><br />M. Bastin suggests in [2]
tojust keep a list in the client app. In my case this would mean modifying PHP to keep the list together with its pool
ofconnections. I guess one could modify PHP, but it seems better and simpler to just modify pgsql to return the list
(thepgsql connection presumably already keeps the list); that would fix the problem also for other programming
languagesusing persistent pgsql connections.<br /><br />I don't know what the interface should be; a possibly ignorant
suggestionis implementing a build-in stored procedure such that "SELECT * FROM _prepared_statement_list() WHERE name
LIKE'a%'" could return a table of (name TEXT, query TEXT) rows?<br />Another possibility would be using something like
"\d"in the psql client (however this is implemented in pgsql), but the IMO the stored procedure approach is better,
as<br/>-You can use SELECT and WHERE clause to filter the result.<br /> -The query will be directly available to for
examplePHP, without having to add a new postgresql function to access it. If fx PHP really wants such a function, they
canimplement it by just executing such a query.<br /><br /> I am not a pgsql hacker, so I am probably not going to
implementthis myself. But it would be nice if the pgsql project added it to the pgsql TODO list. :)<br /><br />[1] <a
href="http://archives.postgresql.org/pgsql-novice/2004-07/msg00089.php">http://archives.postgresql.org/pgsql-novice/2004-07/msg00089.php</a><br
/>[2]<a
href="http://archives.postgresql.org/pgsql-novice/2004-07/msg00090.php">http://archives.postgresql.org/pgsql-novice/2004-07/msg00090.php</a><br
/><br/>Regards, Thue
