Setting Variables on the VSQL Command Line: Quick Tip

Posted September 24, 2018 by Phil Molea, Sr. Information Developer, Vertica

Designers discussing project in office sample library
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!