Setting Variables on the VSQL Command Line: Quick Tip

Jim Knicely authored this post. 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/latest/HTML/index.htm#Authoring/ConnectingToVertica/vsql/Variables.htm https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/ConnectingToVertica/vsql/CommandLineOptions/vAssignment–setAssignment–variableAssignment.htm Have fun!