perl - 如何使用 postgres dbd 占位符作为数据库对象名称

Question

(或如何使用 perl DBI (DBD::PG) 和占位符迭代信息模式？)

Windows 7、ActiveState Perl 5.20.2、PostgreSQL 9.4.1。

下面的案例 A、B 和 C 在为 COLUMN VALUE 使用占位符时是成功的。按顺序

• 没有使用占位符

• 传递了一个文字

• 传递了一个变量(用相同的文字填充)

如果能将它提升到数据库对象的级别会很棒……(表、 View 等)

这是案例 D 的错误输出:

Z:\CTAM\data_threat_mapping\DB Stats\perl scripts>test_placeholder.pl

A Row Count: 1
B Row Count: 1
C Row Count: 1

DBD::Pg::st execute failed: ERROR:  syntax error at or near "$1"

LINE 1: SELECT COUNT(*) FROM $1 WHERE status = 'Draft';
                             ^ at Z:\CTAM\data_threat_mapping\DB     Stats\perl 
scripts\test_placeholder.pl line 34.

非常感谢任何方向!

#!/usr/bin/perl -w
use strict;
use diagnostics;
use DBI;

my $num_rows = 0;

# connect
my $dbh = DBI->connect("DBI:Pg:dbname=CTAM;host=localhost",
                       "postgres", "xxxxx",
                       { 'RaiseError' => 1, pg_server_prepare => 1 });

#---------------------
# A - success
my $sthA = $dbh->prepare(
    "SELECT COUNT(*) FROM cwe_compound_element WHERE status = 'Draft';"
);
$sthA->execute(); # no placeholders

#---------------------
# B -  success
my $sthB = $dbh->prepare (
    "SELECT COUNT(*) FROM cwe_compound_element WHERE status = ?;"
);
$sthB->execute('Draft'); # pass 'Draft' to placeholder

#---------------------
# C -  success
my $status_value = 'Draft';
my $sthC = $dbh->prepare(
    "SELECT COUNT(*) FROM cwe_compound_element WHERE status = ?;"
);
$sthC->execute($status_value); # pass variable (column value) to placeholder

#---------------------
# D - failure
my $sthD = $dbh->prepare(
    "SELECT COUNT(*) FROM ? WHERE status = 'Draft';"
);
$sthD->execute('cwe_compound_element'); # pass tablename to placeholder

我试过单引号/双引号/无引号 (q, qq)...

Answer 1

如果

SELECT * FROM Foo WHERE field = ?

表示

SELECT * FROM Foo WHERE field = 'val'

然后

SELECT * FROM ?

表示

SELECT * FROM 'Table'

这显然是错误的。占位符只能在表达式中使用。修复:

my $sthD = $dbh->prepare("
   SELECT COUNT(*)
    FROM ".$dbh->quote_identifier($table)."
   WHERE status = 'Draft'
");
$sthD->execute();