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.

• Abelisto: Answering to "How can I pass the :v1 parameter to the DO script itself?" here is the trick: outside of the DO execute prepare foo as select :'v1'; and then, inside the DO block ... execute 'execute foo' into this_schema;. To remove prepared statement execute deallocate foo;
• Abelisto: Yet another way: session variables. Outside of DO: set foo.bar to :v1; (dot in the name is required) and inside DO block this_schema := current_setting('foo.bar');
• GT.: @Abelisto could you please make this an answer so it can be marked as the answer? I didn't get the prepare version to work, but didn't spend much time on it: plus, the set version is more parsimonious, and can be included in the DECLARE block if a variable type declaration is added, i.e., this_schema TEXT := current_setting('foo.bar'); . Thanks, GT.

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

• sanchin: When the value "v1" is not passed in, the script errors out with "syntax error at or near ":" LINE 1: SET att.cacheHostsInput to :cacheHostsInput;" How do we make sure that the script run with some default value if the attribute is not passed in, i.e., optional parameters?
• Abelisto: @sanchin Answer updated

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.

• GT.: I hadn't realised that it would be so much easier to pass command-line params to a function as opposed to a DO: given that the schema-creation stuff happens dozens of times a month it should be a function. Dollar quotes are something I really need to get a handle on, too.
• Erwin Brandstetter: @GT.: Note that the previous version had a sneaky bug that would only show for non-standard names: Queries to the system catalogs or the information schema must use unescaped strings. Also simplified some more.