如何使用IN（）参数执行PostgreSQL PREPARE查询

Question

I'm trying to prepare a query from PHP like:

pg_prepare($con, "prep", "select * from test where tid in ($1)");

and then execute it with:

$strpar = "3,4,6,8,10";
pg_execute($con, "prep", array($strpars));

The problem is that I cannot pass a series of values built as prepare expects a fixed number of parameters. Is there any way to make the parameters dynamic?

Answer 1

Use an array to represent the series of values:

pg_prepare($con, "prep", "select * from test where tid=ANY($1)");

$strpar = "{3,4,6,8,10}";
pg_execute($con, "prep", array($strpars));

You can also create a PHP function to receive a PHP array and set it as a valid array for a Postgres prepared statement as in:

function php_array_to_pg ($array) {

    $values = "";

    foreach ($array as $value) {
        if ($values=="") {
        $values = $value;
        } else {
        $values = $values.",".$value;
        }
    }

    return "{".$values."}";
}

Then you make a statement such as:

pg_prepare($con, "prep", "select * from test where tid=ANY($1)");

$array = array(1,2,3,4);

pg_execute($con, "prep", array(php_array_to_pg ($array)));