主页 / dba / 问题 / 19291
Accepted
Jmoney38
Asked: 2012-06-15 12:31:47 +0800 CST

将参数传递给 psql

  • 772

我在 Postgres 8.3 中运行一个 plpgsql 脚本 - 我想通过 psql 将参数传递给这个脚本。我目前正在执行脚本，例如：

psql -d database -u user -f update_file.sql

我遇到了解释 PGOPTIONS 环境变量的此链接，但这不适用于“自定义”参数。即我收到一个错误，因为该设置未在 postgres.conf 文件中列出。

-bash-3.2$ export PGOPTIONS='--pretend=true'
-bash-3.2$ psql -d my_db -f update_database.sql
psql: FATAL:  unrecognized configuration parameter "pretend"

还有其他想法吗？理想情况下，我想避免环境变量...

postgresql psql

6 个回答

  1. 要为 ... 添加其他功能-v如果您尝试添加引号，请在命令行中添加：

    psql -v action="'drop'"

    这将运行以下代码：

    select * where :action;

    一样

    select * where 'drop';
    • 7

  2. 尝试-v：

    $ psql -U postgres -v something=\'blah-blah\'
psql (9.1.3)
Type "help" for help.

postgres=# select :something;
 ?column?
----------
 blah-blah
(1 row)

    如果要使用current_settingandSET或setval，则必须附加一行postgresql.conf以添加选项。

    • 5
  3. Best Answer

    严格来说，没有“plpgsql 脚本”之类的东西——PL/pgSQL 是 PostgreSQL 的默认过程语言。它是 SQL 脚本或 plpgsql 函数/过程。您的示例似乎表明了一个 SQL 脚本。

    您可以创建一个（服务器端）plpgsql（或 sql）函数，它接受任意数量的参数。只要参数是 ，这非常简单values。如果参数包含标识符，它会变得有点复杂。然后，您必须将 PL/pgSQL 与动态 SQL 和EXECUTE.

    PL/pgSQL 默认预装在 PostgreSQL 9.0 或更高版本中。但是，您必须在 Postgres 8.3 中为每个数据库安装一次：

    CREATE LANGUGAGE plpgsql;

    说到版本：您应该考虑升级到当前版本的 PostgreSQL。v8.3 现在已经很老了，在 2013 年初停止使用。

    由于您似乎有一个现成的 SQL 脚本，我将演示一个 SQL 函数。具有两个整数参数的简单虚拟函数：

    CREATE OR REPLACE FUNCTION func(int, int)
    LANGUAGE sql RETURNS void AS 
$func$
    UPDATE tbl1 SET col1 = $1 WHERE id = $2;
    UPDATE tbl2 SET col1 = $1 WHERE id = $2;
$func$;

    您可以在 dba.SE或SO上找到许多更复杂的 plpgsql 示例。

    您可以调用此函数并在 shell 脚本中提交参数：在 shell 脚本中调用的基本示例，它使用整数参数的输入参数（需要的值周围不需要单引号）：

    psql mydb -c "SELECT func($1, $2)"

    或任何数据类型：

    psql mydb -c "SELECT func2('$1'::text, '$2'::numeric)"

    -c执行一个命令字符串然后退出。更多关于手册中 psql 的命令行参数的信息。

    • 5

  4. 根据我的经验，在 plpgsql 声明中取消引用 psql 变量，例如在 CREATE FUNCTION BEGIN 或 DO BEGIN 中会导致语法错误：

    /tmp $ psql -U jmindek -v action=drop
psql (9.3.4)
Type "help" for help.

jmindek=# select :'action';
 ?column? 
----------
 drop
(1 row)

jmindek=# DO $$ BEGIN RAISE INFO 'The value in variable action is (%)',:x; END $$;     
ERROR:  syntax error at or near ":"
LINE 1: ... RAISE INFO 'The value in variable action is (%)',:x; END $$...

    我的解决方案是创建一个带有单列的临时表并将值存储在其中。这个临时表可以通过 plpgsql 访问，因此我可以传递在 DO 块中使用的 psql 变量。

     ~ $ psql -v action=drop
psql (9.3.4)
Type "help" for help.

jmindek=# create temporary table actions (type text);                                                             CREATE TABLE
jmindek=# insert into actions values (:'action');                                                                 INSERT 0 1
jmindek=# do $$                                                                                                   declare                                                                                                            action_type text := null;                                                                                        begin                                                                                                               select type from actions into action_type;                                                                        raise info 'Hello, the action is (%)',action_type;                                                              end $$;
INFO:  Hello, the action is (drop)
DO
jmindek=#

    要在 CREATE FUNCTION 或 DO 声明中使用额外的 psql 变量，您可以为所需的每个变量创建一个列。

    • 2

  5. 这不是很优雅，但它可以工作（伪代码）：

    cat <<EOF
   UPDATE tablename SET field=$arg1 WHERE field = $arg2;
EOF | psql database
    • 0

  6. 这种方法将为您提供 env vars 的完整运行时分辨率......因此，一旦您的脚本预先设置了下面的所有 shell 变量，它将起作用（已针对不同的 dbs 和主机运行了数千次）：

        -- start run.sh

       # 01 create / modify the app user
       sql_script="$pgsql_scripts_dir/01.create-qto-app-user.pgsql"
       PGPASSWORD="${postgres_db_useradmin_pw:-}" psql -q -t -X -w -U "${postgres_db_useradmin:-}" \
          -h $postgres_db_host -p $postgres_db_port \
          -v ON_ERROR_STOP=1 \
          -v postgres_db_user_pw="${postgres_db_user_pw:-}" \
          -v postgres_db_name="${postgres_db_name:-}" \
          -f "$sql_script" "${postgres_db_name:-}" > "$tmp_log_file" 2>&1
       ret=$?
       cat "$tmp_log_file" ; cat "$tmp_log_file" >> $log_file # show it and save it
       test $ret -ne 0 && sleep 3
       test $ret -ne 0 && doExit 1 "pid: $$ psql ret $ret - failed to run sql_script: $sql_script !!!"
    -- stop run.sh

    -- start fun.sql
            DO
            $do$
            BEGIN
               IF NOT EXISTS (
                  SELECT
                  FROM   pg_catalog.pg_roles
                  WHERE  rolname = 'usrqtoapp') THEN
                     CREATE ROLE usrqtoapp WITH PASSWORD ':postgres_db_user_pw' LOGIN ;
               END IF;
            END
            $do$;
            ALTER ROLE usrqtoapp WITH PASSWORD  :'postgres_db_user_pw' LOGIN ;

    -- eof run.sql
    • 0

相关问题