How to parametrize SWAP_PARTITIONS_BETWEEN_TABLES

Answers

• marcothesane - Select Field - Administrator

  March 2020

  Try this script with vsql:

  \o | vsql -a
  \a
  \t
  SELECT
    'SELECT SWAP_PARTITIONS_BETWEEN_TABLES(''A_MART.SALES_DATA_STAGE'','
  ||QUOTE_LITERAL(SELECT START_DATE FROM A_TMP.START_END_DATE LIMIT 1)
  ||','||QUOTE_LITERAL(SELECT END_DATE FROM A_TMP.START_END_DATE LIMIT 1)
  ||',''A_MART.SALES_DATA'',TRUE);'
  FROM timerange;
  

  0
• marcothesane - Select Field - Administrator

  March 2020

  SWAP_PARTITIONS_BETWEEN_TABLES works with literals, and I never got it working with any expression ....

  0
• avoronin ✭

  March 2020

  I have done it with SSIS expressions even before this post. Just was interested if its possible to do it without sql injections. Vertica should implement parameters and procedures. That's a long time want already.

  "DROP TABLE IF EXISTS A_TMP.START_END_DATE;
  CREATE TABLE A_TMP.START_END_DATE AS
  SELECT " + (DT_WSTR, 99999)(@[User::StartDateInt]) + " AS START_DATE, " + (DT_WSTR, 99999)(@[User::EndDateInt]) + " AS END_DATE;"

  0
• marcothesane - Select Field - Administrator

  March 2020

  Parameters are possible when using vsql. And that's the way it should be, as it is a client's need, not a database platform's need.
  you can set a parameter's name on the command line - and use it in a sql command:
  $ echo 'select :foo;' | vsql -v foo="'bar'"
?column?
----------
bar
(1 row)

  0
• avoronin ✭

  March 2020 edited March 2020

  marcothesane, that's certainly not the way it should be. There are various client tools, etl tools, client apps that want to access vertica. MS SQL, ORACLE, SAP BW, MySQL -- all have parameters in ADO.NET or OLEDB drivers. That Vertica should not is a strange policy that actually may limit vertica's use and might avert potential customers. Accessing DB via shell is far from the common database use. I do not come to Linix shell at all and do not want to.

  0