How to parametrize SWAP_PARTITIONS_BETWEEN_TABLES

avoronin

I have two dates in a certain table which mark the current change set.
I want to switch partitions. The following queries did not work. What is a proper way of passing partition values to SWAP_PARTITIONS_BETWEEN_TABLES or similar functions?

SELECT SWAP_PARTITIONS_BETWEEN_TABLES('A_MART.SALES_DATA_STAGE', START_DATE, END_DATE, 'A_MART.SALES_DATA')
FROM A_TMP.START_END_DATE;

SELECT SWAP_PARTITIONS_BETWEEN_TABLES(
'A_MART.SALES_DATA_STAGE',
(SELECT START_DATE FROM A_TMP.START_END_DATE LIMIT 1),
(SELECT END_DATE FROM A_TMP.START_END_DATE LIMIT 1),
'A_MART.SALES_DATA');

marcothesane

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;

marcothesane

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

avoronin

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;"

marcothesane

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)

avoronin

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.