PHP/PostgreSQL: check if a prepared statement alre

Question

I create my prepared statement as:

pg_prepare('stm_name', 'SELECT ...');

Today, I had a problem (calling twice a function for mistake) when declaring a prepared statement with the same name twice:

Warning: pg_prepare() [function.pg-prepare]: Query failed: ERROR: prepared statement "insert_av" already exists in xxx on line 221

So, as the question title, there is a way to check if a prepare statement with the same label already exists, and in case, overwrite it?

I know this error is from my mistake and will be solved by simply declaring the prepared statements at the begin of my code, but I'm wondering if there is a solution to have more control over them.

EDIT:

After the Milen answer, is quite simply to check if the prepared statement is already in use, simply querying the db for the table pg_prepared_statements:

try{
    $qrParamExist = pg_query_params("SELECT name FROM pg_prepared_statements WHERE name = $1", array($prepared_statement_name));
    if($qrParamExist){
        if(pg_num_rows($qrParamExist) != 0){
            echo 'parametized statement already created';
        }else{
            echo 'parametized statement not present';
        }
    }else{
        throw new Exception('Unable to query the database.');
    }
}catch(Exception $e){
    echo $e->getMessage();
}

But, I don't think this is a good solution, because i have to query the database every time.

Ok, usually the prepared statements are declared in the begin of the script and then just reused, but, I have a class nicely wired and I don't like to declare 10 prepared statements when I'll use just 3 of them.

So, I think I'll use a simple PHP array to keep track the statements I create, and then with isset() function check if it exists or needs to be created:

try{
    $prepare = pg_prepare('my_stmt_name', "SELECT ...");
    if($prepare){
        $this->rayPrepared['my_stmt_name'] = true;
    }else{
        throw new Exception('Prepared statement failed.');
    }
}catch(Exception $e){
    echo $e->getMessage();
}

Answer 1

One way (I hope someone will point out a simpler one):

<?
$prepared_statement_name = 'activity1';
$mydbname = '...';

$conn = pg_connect("host=... port=... dbname=... user=... password=...");

$result = pg_query_params($conn, 'SELECT name FROM pg_prepared_statements WHERE name = $1', array($prepared_statement_name));

if (pg_num_rows($result) == 0) {
    $result = pg_prepare($conn, $prepared_statement_name, 'SELECT * FROM pg_stat_activity WHERE datname =  $1');
}

$result = pg_execute($conn, $prepared_statement_name, array($mydbname));
while($row = pg_fetch_row($result)) {
    var_dump($row);
}

Answer 2

Haven't tried this in php but if this is feasible in your application (if you need the statement only in one place and don't have to "fetch" it again by name) you could try to prepare an unnamed statement. http://www.postgresql.org/docs/8.4/interactive/libpq-exec.html says:

PQprepare
...
stmtName may be "" to create an unnamed statement, in which case any pre-existing unnamed statement is automatically replaced; otherwise it is an error if the statement name is already defined in the current session.

php_pg uses PQprepare, so this might work for you.

Answer 3

Why are you using prepared statements at all ? They only offer a performance advantage if you use the same statement many times over.