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

Answer 2

To be able to pass parameters, create a temporary function (or maybe procedure in Postgres 12+) instead of using a DO statement:

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

   IF EXISTS (SELECT 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$;

Call:

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

From psql with SQL interpolation using a variable v1:

SELECT pg_temp.f_create_schema(:'v1');

The schema name passed for _schema is a case sensitive string (so single-quoted input) containing the bare identifier (without double-quotes).

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.

• Is there such thing as a "temp function"?

It makes sense if you only need the function in the current session. For repeated use in the same database, create a plain, persisted function instead.

Of course, you need the TEMPORARY privilege for the database - which all user roles have by default.

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

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

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

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

• Use nested dollar quotes to avoid quoting hell.

There are all kinds of workarounds, too:

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

Aside: 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.