Get projection create statement only if table has same name.

zxcvmnb

Hi, I created a table and it created a projection with same name.

I need create statement for projections only. 

If I do "select export_object('','schm.tab',true)", I get the DDL for table for table and projections. 

 

Is there a way to let vertica know that I want DDL for projection named schm.tab not the table named schm.tab ?

Adrian_Oprea_1

 You can use a semi-dinamic query .

\t 
-- store sql script here
\o /tmp/exec.sql
-- generate script
with a as (
select 'SELECT EXPORT_OBJECTS('''','''||projection_schema||'.'||projection_name||''',false);' as SQL_SCR,
ROW_NUMBER() over (partition by projection_basename)as prjnum from projections where projection_schema='schema name' and anchor_table_name='table name'
)
select SQL_SCR from a where prjnum = 1 ;
-- store def of proj here
\o /tmp/projection_def.sql
-- execute extrat of proj definition
\i /tmp/exec.sql

  - you can even create a custom User procedure to extract the proj definition based on this logic.

 

hope this helped