drop_partition in a script

Pe_Leroy_1

Hello,

I'd like to use the drop_partition in an sql request where I retrieve partition id from the partitions table. I cannot manage to make it. Something like:

WITH partition_to_be_deleted AS  (SELECT DISTINCT PARTITION_KEY FROM partitions WHERE TABLE_SCHEMA='TEST' AND PROJECTION_NAME LIKE 'MY_TABLE%' AND PARTITION_KEY < ....))
SELECT DROP_PARTITION('TEST1.MY_TABLE', partition_nb)WHERE partition_nb IN (SELECT PARTITION_KEY FROM partition_to_be_deleted);

Is it possible?

Tx

Pe_Leroy_1

I answer to myself.... in case other people are interested. Meta-functions like drop_partition() cannot be used in sql scripts.

The solution I found is a bash script: vsql -At -h ${DB_HOST} -U ${DB_ADMIN_USER} -w ${DB_ADMIN_PASS} -c "SELECT DISTINCT PARTITION_KEY FROM partitions WHERE TABLE_SCHEMA='${schema}' AND PROJECTION_NAME LIKE '${tables}%' AND PARTITION_KEY < ${part_nb} ;" |  xargs -I{} bash -c "vsql -h ${DB_HOST} -U ${DB_ADMIN_USER} -w ${DB_ADMIN_PASS} -c \"SELECT DROP_PARTITION(${tables},{}\""   Hope it helps.