sqlverticavsql

Variable in sql file with single quote

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

Solution

  • 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,...);