Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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('','',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 not the table named ?


  •  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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.