prepare()

Is there an advantage to using something like $dbh->prepare($sql) if
the SQL is going to be run once within the scope of the code? The
code block may be run many times in a minute as in a function call ---

while (<>) {
insert_something($_);
}

Will the prepare statement be cached @ the database even if it's
destroyed in the code/application?

The pg_prepared_statments view will give you information on prepared statments
currently resident in the database backend. I suggest you run your progam
(with a couple of "Enter to continue" breakpoints in the code) and, in another
session, select from pg_prepared_statements and see if the prepared statement
is still there.

I'll be trying this myself a bit later once I stabilise my system, so
watch this space!

Cheers,
Stuart.

The pg_prepared_statments view will give you information on prepared statments
currently resident in the database backend. I suggest you run your progam
(with a couple of "Enter to continue" breakpoints in the code) and, in another
session, select from pg_prepared_statements and see if the prepared statement
is still there.

I'll be trying this myself a bit later once I stabilise my system, so
watch this space!

My system is stabilised and I'm looking at this now. I'm not getting a lot from
pg_prepared_statements yet: perhaps this view only reports on statements you've
prepared using PostgreSQL's PREPARE through their SQL interface, and not
DBI's DBD::Pg $dbh->prepare().

I know that a few versions back PostgreSQL's DBD driver didn't support prepared
statements (the operation was still there, it just wasn't doing the
whole magic),
I imagine that it does today but will investigate further.

Cheers,
Stuart.

I know that a few versions back PostgreSQL's DBD driver didn't support prepared
statements (the operation was still there, it just wasn't doing the
whole magic),
I imagine that it does today but will investigate further.

There's a very extensive writeup in the documentation of DBD::Pg,
perldoc DBD::Pg
and read the large section on the prepare() method.

Cheers,
Stuart.

"Stuart Cooper" <stuart.cooper@gmail.com> writes:

My system is stabilised and I'm looking at this now. I'm not getting a
lot from pg_prepared_statements yet: perhaps this view only reports on
statements you've prepared using PostgreSQL's PREPARE through their
SQL interface, and not DBI's DBD::Pg $dbh->prepare().

A quick look at the source code says that pg_prepared_statements should
show both statements prepared with the SQL-level PREPARE command, and
statements prepared through the wire-protocol Parse message (excluding
the "unnamed" statement in the latter case). I'm not familiar with the
guts of DBD::Pg, however; it may not be "preparing" statements in any
sense that the backend knows about, but only massaging them locally to
the client library. It likely matters which version of DBD::Pg you're
talking about, too, because the backend's support for this sort of thing
has been a moving target.

regards, tom lane