Variable in sql file with single quote

Question

SELECT SWAP_PARTITIONS_BETWEEN_TABLES
(':SCHEMA_NAME.:TABLE_NAME',:PARTITION_KEY,:PARTITION_KEY,
':SCHEMA_NAME.:TABLE_NAME');

This is a vertica query in sql file :SCHEMA_NAME and :TABLE_NAME in sql file is not getting replaced by the argument passed probably because of single quotes

Would https://stackoverflow.com/questions/26349613/add-quotes-around-variable help ? — Kalpa Welivitigoda, Aug 08 '17 at 06:23
there is only one variable in single quote now i need 2 variables concatenated within a single quote @KalpaWelivitigoda — Swathi8038, Aug 08 '17 at 06:39
Would something like, '''' + :SCHEMA_NAME + '''' + '.' + '''' + :TABLE_NAME+ '''' work? I didn't try though. — Kalpa Welivitigoda, Aug 08 '17 at 06:45
SCHEMA_NAME = abc TABLE_NAME = xyz it took like 'abc'.'xyz' i want it as 'abc.xyz' — Swathi8038, Aug 08 '17 at 07:00
Sry my bad, try '''' + :SCHEMA_NAME + '.' + :TABLE_NAME+ '''' — Kalpa Welivitigoda, Aug 08 '17 at 07:01

mauro · Accepted Answer · 2017-08-08T08:11:04.527

3

Try this:

\set source '''src_schema.src_table'''
\set target '''tgt_schema.tgt_table'''
SELECT SWAP_PARTITIONS_BETWEEN_TABLES
    (:source,:PARTITION_KEY,:PARTITION_KEY,:target);

If you want to use different variables for SCHEMA and TABLE, you can:

\set schema '''myschema'''
\set table '''mytable'''

And then:

SELECT SWAP_PARTITIONS_BETWEEN_TABLES
    (:schema||'.'||:table,:PARTITION_KEY,...);

edited Aug 08 '17 at 08:11

answered Aug 08 '17 at 07:54

mauro

5,730
2
26
25

this works but i have to give schema name and table name within quotes :schema||'.'||:table within quotes – Swathi8038 Apr 11 '18 at 06:02

Variable in sql file with single quote

1 Answers1