Php: check if an pg_prepare prepared statement already exist

Question

Hi guys,

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 come for a my mistake and will be solved simply declaring the prepared statements at the begin of my code, but im 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 queryin 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 dont think this is a good solution, becose i have to query the database everytime.

Ok, usually the prepared statements are declarated in the begin of the script and then just reutilized, but, i have a class nicely wired and i dont like to declare 10 prepared statement 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 exist or need 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.