Setting Variables on the VSQL Command Line

Jim_Knicely

The vsql client provides variable substitution features similar to common Linux command shells. Variables are name/value pairs, where the value can be a string of any length. The -v, --variable, and --set options can be used to set a variables value from the command line.

Example:

The following simple SQL script creates a table in a schema which is identified by the variable “schema_name”:

[dbadmin@s18384357 ~]$ cat create_test_table.sql
DROP TABLE IF EXISTS :schema_name.test_table CASCADE;
CREATE TABLE :schema_name.test_table (c1 INT);

I can pass a value to the “schema_name” variable in the SQL script using the vsql -v command line option:

[dbadmin@s18384357 ~]$ vsql -v schema_name=production -ef create_test_table.sql
DROP TABLE IF EXISTS production.test_table CASCADE;
vsql:create_test_table.sql:1: NOTICE 4185:  Nothing was dropped
DROP TABLE
CREATE TABLE production.test_table (c1 INT);
CREATE TABLE

You can also set built-in vsql variables from the command line!

[dbadmin@s18384357 ~]$ vsql -v ON_ERROR_STOP=ON -c "\echo :ON_ERROR_STOP"
ON

[dbadmin@s18384357 ~]$ cat fail2.sql
-- load tbl1
delete from tbl1 where pkid in (select pkid from tbl1_temp);
insert into tbl1 select * from tbl1_temp;
-- load tbl2
delete from tbl2 where pkid in (select pkid from tbl2_temp);
insert into tbl2 select * from tbl2_temp;

COMMIT;
--Rollback if one of the stmts fail

[dbadmin@s18384357 ~]$ vsql -v ON_ERROR_STOP=ON -ef fail2.sql
delete from tbl1 where pkid in (select pkid from tbl1_temp);
OUTPUT
--------
      0
(1 row)

insert into tbl1 select * from tbl1_temp;
vsql:fail2.sql:3: ERROR 2501:  Cannot set a NOT NULL column (pkid) to a NULL value in INSERT/UPDATE statement

Helpful links:
https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/ConnectingToVertica/vsql/Variables.htm
https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/ConnectingToVertica/vsql/CommandLineOptions/vAssignment--setAssignment--variableAssignment.htm

Have fun!