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
passing string/date variables from shell to sql file
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: passing string/date variables from shell to sql file
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
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.