The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

Setting Variables on the VSQL Command Line

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”:

[[email protected] ~]$ 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:

[[email protected] ~]$ 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!

[[email protected] ~]$ vsql -v ON_ERROR_STOP=ON -c "\echo :ON_ERROR_STOP"
ON

[[email protected] ~]$ 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

[[email protected] ~]$ 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!

Sign In or Register to comment.

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.