DO脚本中的PSQL命令行参数 [英] PSQL Command Line Arguments in DO script

问题描述

我有一个脚本NewSchemaSafe.sql，它可以根据项目目录创建一个新的模式.从Windows命令行中调用它，如下所示:

I've got a script NewSchemaSafe.sql that creates a new schema based on the project directory; it's called from the Windows command line as follows:

for %%a in (.) do set this=%%~na
-- other stuff here
psql -U postgres -d SLSM -e -v v1=%this% -f "NewSchemaSafe.sql"

NewSchemaSafe.sql如下:

-- NewSchemaSafe.sql
-- NEW SCHEMA SETUP 
--    - checks if schema exists
--    - if yes, renames existing with current monthyear as suffix
-- NOTE: will always delete any schema with the 'rename' name (save_schema)
--       since any schema thus named must have resulted from this script 
--       on this date - so, y'know, no loss.
SET search_path TO :v1, public; -- kludge coz can't pass :v1 to DO
DO
$$
DECLARE
       this_schema TEXT:= current_schema()::TEXT;
       this_date TEXT:= replace(current_date::TEXT,'-','');
       save_schema TEXT:= this_schema||this_date;
BEGIN
    IF this_schema <> 'public'
    THEN
        RAISE NOTICE 'Working in schema %', this_schema;
        IF EXISTS(
            SELECT schema_name
              FROM information_schema.schemata
              WHERE schema_name = save_schema)
        THEN
           EXECUTE 'DROP SCHEMA '||save_schema||' CASCADE;';
        END IF;
        IF NOT EXISTS(
            SELECT schema_name
              FROM information_schema.schemata
              WHERE schema_name = this_schema
          )
        THEN
          EXECUTE 'CREATE SCHEMA '||this_schema||';';
        ELSE
          EXECUTE 'ALTER SCHEMA '||this_schema|| ' RENAME TO '|| save_schema ||';';
          EXECUTE 'COMMENT ON SCHEMA '|| save_schema ||' IS ''schema renamed by SLSM creation on '|| this_date ||'''';
          EXECUTE 'CREATE SCHEMA '||this_schema||';';
        END IF;
    ELSE
        RAISE NOTICE 'SCHEMA IS % SO PARAMETER WAS NOT PASSED OR DID NOT STICK', this_schema;
    END IF;
END
$$;

现在我知道SET发生了，因为我可以在命令行输出中看到它.但是，该脚本的其余部分都死了(按预期方式优美地终止了)，因为它似乎认为current_schema是public:该脚本产生了

Now I know that the SET happens, because I can see it on the command-line output. However the rest of the script dies (gracefully, as intended) because it seems to think that current_schema is public: the script yields

psql: NewSchemaSafe.sql:39: NOTICE:  SCHEMA IS public SO PARAMETER WAS NOT PASSED OR DID NOT STICK

我最初尝试将:v1传递给DO循环的DECLARE块，如下所示:

I had initially tried to pass :v1 to the DECLARE block of the DO loop as follows:

 DECLARE
       this_schema text := :v1 ;
       this_date text := replace(current_date::text,'-','');
       save_schema text := this_schema||this_date;
  [snip]

但这只是在葡萄藤上消失:它引发语法错误-

But that just dies on the vine: it throws a syntax error -

psql:NewSchemaSafe.sql:40: ERROR:  syntax error at or near ":"
LINE 4:        this_schema text := :v1 ;

无论%this%用引号引起来还是不存在于批处理文件中，都没有区别.

It does not make a difference if the %this% is enclosed in quotes or not in the batch file.

和往常一样，有两个问题:

So as usual, two questions:

1. 当我可以的时候，set search path语句为什么不粘" 看到它正在执行?更新:不相关，请忽略.
2. 如何将:v1参数传递给DO脚本本身?

1. How come the set search path statement doesn't 'stick', when I can see it executing? UPDATE: not relevant, pls ignore.
2. How can I pass the :v1 parameter to the DO script itself?

环境:PostgreSQL 9.3.5 64位(Win);

Environment: PostgreSQL 9.3.5 64-bit (Win);

怪异:我确信此脚本在两天前就可以使用了，唯一的变化是删除了geany插入的字节顺序标记(UTF BOMs使psql gag).

Weirdnesses: I am certain that this script worked two days ago, and the only change was to remove the byte-order-mark inserted by geany (UTF BOMs make psql gag).

更新:前一天它起作用的原因是，它在所考虑的模式 did 存在的情况下运行.如果以:v1 传递的模式名称不存在，则更改search_path(尝试从current_schema伪装所需的模式)将无济于事-这使得:v1传递给DO，因此可以更直接地使用它.

UPDATE: the reason it worked the other day was that it was being run in a situation where the schema under consideration did exist. Changing search_path (to try and finagle the desired schema from current_schema) won't help if the schema name being passed as :v1 doesn't exist - that makes it more important that :v1 gets passed to the DO so it can be used more directly.

推荐答案

创建临时的功能，而不使用DO语句.如果您需要传递参数，那就是 解决方案 .

Create a temporary function instead of using a DO statement. That's the solution if you need to pass parameters.

CREATE FUNCTION pg_temp.f_create_schema(_schema text)  -- note function schema "pg_temp"
  RETURNS void AS 
$func$
DECLARE
   _date        text := to_char(current_date, 'YYYYMMDD');
   _save_schema text := _schema || _date;  -- unescaped identifier
BEGIN
   IF EXISTS (SELECT 1 FROM information_schema.schemata
              WHERE  schema_name = _save_schema) THEN  -- unescaped identifier
      EXECUTE format('DROP SCHEMA %I CASCADE', _save_schema);  -- escaped identifier!
   END IF;

   IF EXISTS (SELECT 1 FROM information_schema.schemata
              WHERE  schema_name = _schema) THEN
      EXECUTE format(
        'ALTER SCHEMA %1$I RENAME TO %2$I;
         COMMENT ON SCHEMA %2$I IS $c$Schema renamed by SLSM creation on %3$s.$c$'
       , _schema, _save_schema, _date);
   END IF;

   EXECUTE 'CREATE SCHEMA ' || quote_ident(_schema);
END
$func$ LANGUAGE plpgsql;

致电:

SELECT pg_temp.f_create_schema('Foo');  -- function name must be schema-qualified

通过带有 SQL插值使用变量v1:

SELECT pg_temp.f_create_schema(:'v1');

您为_schema传递的架构名称是区分大小写和不带引号的.

The schema name you pass for _schema is case sensitive and unquoted.

pg_temp是一个伪名称，它在内部自动转换为当前会话的临时模式.临时模式中的所有对象都在会话的 处死亡.

pg_temp is a pseudo name that translates to the temporary schema of the current session internally automatically. All objects in the temporary schema die at the end of the session.

临时"功能未在手册中明确记录，但使用安全.

"Temporary" functions are not documented explicitly in the manual, but safe to use.

• 是否存在诸如功能" ??

如果您需要在同一会话中一次(或几次)使用该功能来处理各种数据库，那么这是很有意义的.要在同一数据库中重复使用，请创建一个普通函数.

It makes sense if you need to the function once (or a few times) in the same session for varying databases. For repeated use in the same database, create a plain function instead.

当然，您需要具有 TEMPORARY特权数据库-用户默认情况下拥有的数据库.

Of course you need the TEMPORARY privilege for the database - which users have by default.

在做到这一点的同时，我改善了几件事:

While being at it, I improved a couple of things:

• 转义符以防御 SQL注入和普通语法错误.使用 quote_ident()或format() 处理更复杂的事情.

• Escape identifiers to defend against SQL injection and ordinary syntax errors. Use quote_ident() or format() for anything more complex.

您不需要将分号连接到单个SQL命令的末尾.

You don't need to concatenate a semicolon to the end of a single SQL command.

您可以一次EXECUTE多个SQL语句. (现在，您需要在 语句之间使用分号.)

You can EXECUTE multiple SQL statements at once. (Now you need a semicolon between statements.)

使用嵌套的美元报价来避免引用地狱.

也有各种各样的 解决方法 :

There are all kinds of workarounds, too:

• 使用变量集通过DO块内的psql元命令
• 使用带有psql参数变量

• Use variable set by psql meta-command inside of DO block
• CREATE SEQUENCE using expressions with psql variables for parameters

BTW，自定义选项(会话变量")出于历史原因，需要由两部分组成的名称(格式为 extension.variable ).事实证明，它在尽可能避免命名冲突方面很有用.

BTW, customized options ("session variables") require a two-part name (of the form extension.variable) for historic reasons. It proved to be useful in avoiding naming conflicts as much as possible.

这篇关于DO脚本中的PSQL命令行参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！