Get projection create statement only if table has same name.

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 ?


  • Options

     You can use a semi-dinamic query :).

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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file