We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


Setting Variables on the VSQL Command Line — Vertica Forum

Setting Variables on the VSQL Command Line

Jim_KnicelyJim_Knicely - Select Field - Administrator

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!

Sign In or Register to comment.