passing string/date variables from shell to sql file

Moderator: NorbertKrupa

Post Reply
uchimoto
Newbie
Newbie
Posts: 1
Joined: Wed Nov 20, 2013 6:53 pm

passing string/date variables from shell to sql file

Post by uchimoto » Tue Dec 02, 2014 3:13 am

How can I pass variables that require wrapping in single quotes (i.e. strings and dates) to a sql file using vsql from a bash command? vsql parameter substitution works fine for types that don't require single quote wrapping (int, numeric) in the sql, but I haven't found any examples that demonstrate usage with a type requiring quotes.

var_test.sql:
select table_name from tables where table_name = :table_name;

command:
$ vsql -d my_db -h localhost -w my_pwd -f ./var_test.sql -v table_name=my_table_name -e

The example above passes the :table_name variable in from the command line, but the sql throws an error because the table name is not wrapped in single quotes. If I wrap the :table_name variable in single quotes, the parameter substitution breaks.

Thanks

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: passing string/date variables from shell to sql file

Post by JimKnicely » Tue Dec 02, 2014 1:27 pm

Using double quotes and single quotes to enclose the table name seems to work:

Code: Select all

[dbadmin@vertica01 ~]$ vsql -c "create table public.var_test(c int);"
CREATE TABLE

[dbadmin@vertica01 ~]$ cat var_test.sql
select table_name from tables where table_name = :table_name;

[dbadmin@vertica01 ~]$ vsql -f ./var_test.sql -v table_name="'var_test'" -e
select table_name from tables where table_name = 'var_test';
 table_name
------------
 var_test
(1 row)
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “vSQL”