Perl脚本调用psql执行DO块时未终止美元引号错误求助

我在Perl脚本中通过以下方式执行SQL语句：

my $cmd = "PGPASSWORD=$pw  /usr/bin/psql  -h ${host} -p ${port} -d ${dbname} -U ${user} -c '${dropTblsSql}' -q  >> logfile.log"
my $out = `$cmd`;

其中SQL语句存储在$dropTblsSql变量中：

my $dropTblsSql = "
DO $$
 DECLARE 
  r record;
BEGIN
   FOR r IN SELECT quote_ident(tablename) AS tablename, quote_ident(schemaname) AS schemaname FROM pg_tables WHERE schemaname = 'rbm_pu7_mdt'
   LOOP
     EXECUTE format('DROP TABLE IF EXISTS %I.%I CASCADE', r.schemaname, r.tablename);
   END LOOP;
END;
$$ LANGUAGE plpgsql;
";

执行时出现Unterminated dollar-quoted string at or near "$$"错误。我尝试过转义美元符号（\$\$）、将其替换为单引号（并转义其他单引号），但均无效。仅通过文件执行（-f path_to_file）可成功，但我希望避免该方式。请问如何解决双美元引号字符串的问题？

方法1：调整psql参数的引号包裹方式 #

将psql的-c参数改用双引号包裹SQL内容，同时转义SQL中的$$为\$\$，避免Perl和shell解析冲突。修改后代码如下：

my $dropTblsSql = "
DO \$\$
 DECLARE 
  r record;
BEGIN
   FOR r IN SELECT quote_ident(tablename) AS tablename, quote_ident(schemaname) AS schemaname FROM pg_tables WHERE schemaname = 'rbm_pu7_mdt'
   LOOP
     EXECUTE format('DROP TABLE IF EXISTS %I.%I CASCADE', r.schemaname, r.tablename);
   END LOOP;
END;
\$\$ LANGUAGE plpgsql;
";

my $cmd = "PGPASSWORD=$pw  /usr/bin/psql  -h ${host} -p ${port} -d ${dbname} -U ${user} -c \"${dropTblsSql}\" -q  >> logfile.log";
my $out = `$cmd`;

原理：双引号包裹的shell参数不会将内部单引号视为边界，转义后的\$\$会被shell正确传递给psql，识别为PL/pgSQL的美元引号。

方法2：使用shell转义工具处理SQL字符串 #

借助Perl的String::ShellQuote模块自动转义所有shell特殊字符，无需手动处理符号：

use String::ShellQuote;

my $dropTblsSql = "
DO $$
 DECLARE 
  r record;
BEGIN
   FOR r IN SELECT quote_ident(tablename) AS tablename, quote_ident(schemaname) AS schemaname FROM pg_tables WHERE schemaname = 'rbm_pu7_mdt'
   LOOP
     EXECUTE format('DROP TABLE IF EXISTS %I.%I CASCADE', r.schemaname, r.tablename);
   END LOOP;
END;
$$ LANGUAGE plpgsql;
";

my $escaped_sql = shell_quote($dropTblsSql);
my $cmd = "PGPASSWORD=$pw  /usr/bin/psql  -h ${host} -p ${port} -d ${dbname} -U ${user} -c $escaped_sql -q  >> logfile.log";
my $out = `$cmd`;

该模块会自动处理所有可能导致shell解析错误的字符，确保SQL完整传递给psql。

方法3：改用Perl PostgreSQL驱动执行（推荐） #

直接使用DBD::Pg模块连接数据库执行SQL，彻底绕开shell转义问题，代码更安全可靠：

use DBI;
use DBD::Pg;

# 建立数据库连接
my $dbh = DBI->connect(
    "dbi:Pg:dbname=$dbname;host=$host;port=$port",
    $user,
    $pw,
    {AutoCommit => 1}
) or die DBI->errstr;

my $dropTblsSql = "
DO $$
 DECLARE 
  r record;
BEGIN
   FOR r IN SELECT quote_ident(tablename) AS tablename, quote_ident(schemaname) AS schemaname FROM pg_tables WHERE schemaname = 'rbm_pu7_mdt'
   LOOP
     EXECUTE format('DROP TABLE IF EXISTS %I.%I CASCADE', r.schemaname, r.tablename);
   END LOOP;
END;
$$ LANGUAGE plpgsql;
";

# 执行SQL
$dbh->do($dropTblsSql) or die $dbh->errstr;

# 断开连接
$dbh->disconnect;

这种方式直接与数据库交互，无需处理任何shell转义，同时避免了命令行执行可能带来的安全风险。

内容的提问来源于stack exchange，提问作者JanFi86