我有一个脚本NewSchemaSafe.sql
根据项目目录创建一个新模式;它是从 Windows 命令行调用的,如下所示:
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
is public
:脚本产生
psql: NewSchemaSafe.sql:39: NOTICE: SCHEMA IS public SO PARAMETER WAS NOT PASSED OR DID NOT STICK
我最初试图通过:v1
to the DECLARE
块的DO
循环如下:
DECLARE
this_schema text := :v1 ;
this_date text := replace(current_date::text,'-','');
save_schema text := this_schema||this_date;
[snip]
但这只是中途夭折:它会抛出语法错误 -
psql:NewSchemaSafe.sql:40: ERROR: syntax error at or near ":"
LINE 4: this_schema text := :v1 ;
如果%this%
是否用引号引起来或不在批处理文件中。
像往常一样,有两个问题:
- 怎么来的
set search path
当我可以的时候,声明不会“坚持”see它执行?更新:不相关,请忽略。
- 我怎样才能通过
:v1
参数到DO
脚本本身?
环境:PostgreSQL 9.3.5 64位(Win);
怪异之处:我确信这个脚本在两天前就可以工作,唯一的变化是删除了 geany 插入的字节顺序标记(UTF BOM 使psql
gag).
UPDATE:前几天它工作的原因是它正在考虑模式的情况下运行did存在。改变search_path
(尝试从中欺骗所需的模式current_schema
)如果模式名称传递为:v1
不存在- 这使得更重要的是:v1
被传递到DO
所以可以更直接的使用。