postgresql - PSQL Command Line Arguments in DO script

Question

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 is as follows:

-- 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
$$;

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

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 ;

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. 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?

Environment: PostgreSQL 9.3.5 64-bit (Win);

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).

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.

Answer 1

Because the PL blocks is actually text constants in the code the internal variables is not substituted inside them in the usual way. Fortunately it is possible to use a session variables for sharing data between different SQL/PL blocks:

set foo.bar to :v1; -- Name should contains the dot, don't ask me why 
show foo.bar; -- Check that the value was assigned 
do $$
declare
  myvar text := current_setting('foo.bar');
begin
  raise info '%', myvar; -- Output variable value
end $$;

To ensure that the variable is assigned and set the default value if it is not:

if :{?v1}
  set foo.bar to :v1;
else
  set foo.bar to 'default';
endif

More details:
https://www.postgresql.org/docs/current/app-psql.html#PSQL-METACOMMAND-IF https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-INTERPOLATION