PHP / PostgreSQL：检查准备好的语句是否已经存在 [英] PHP/PostgreSQL: check if a prepared statement already exists

问题描述

我将准备好的语句创建为：

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

因此，作为问题标题，有一种检查是否已经存在带有相同标签的prepare语句，以防万一，将其覆盖？

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.

编辑：

在Milen回答之后，非常简单地检查是否已使用准备好的语句，只需在db中查询表pg_prepared_statements：

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.

好吧，通常在脚本的开头声明准备好的语句，然后重新使用它们，但是，我有一个很好的连接的类，当我只使用3条语句时，我不想声明10条预备语句。

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.

因此，我想我将使用一个简单的PHP数组来跟踪我创建的语句，然后使用 isset（）函数检查该语句是否存在或需要创建：

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();
}

推荐答案

一种方法（我希望有人会指出一个更简单的方法）：

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);
}

这篇关于PHP / PostgreSQL：检查准备好的语句是否已经存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！