在Postgresql中使用模式和表的参数值

Question

I'm trying to use a query where the schema and table are passed as parameters into a prepared statement using pg_query_params like this:

$searchSchema = 'mySchema';
$searchTable = 'myTable';
$searchColumn = 'searchColumn';
$searchValue = 'some value';

$selQuery = "SELECT *

FROM $1.$2  --fails here

WHERE someColumn like $3;";

$rs = pg_query_params($db, $selQuery , array($searchSchema, $searchTable, $searchColumn, $searchValue));

The issue is with the schema and table which need to be set dynamically - as in the code above.

Answer 1

In a parameterized SQL statement (which is a prepared statement in PostgreSQL), parameters can only stand for constant values, not for table or column names.

This limitation is enforced by PostgreSQL, and there is no way around it, no matter what programming language or driver you use. This is also intentional and not a bug.

You will have to compose a string that contains the complete SQL statement with table and column names substituted and execute that. Beware of SQL injection – use functions like pg_escape_identifier to escape names.