Eporting just projection definitions

[becky]

Hey,

How do I export just the projection definitions for a table and not the create table statement? I know I can use the export_objects function, but that includes the table create in the output...

SELECT export_objects('', 'table_name');

[id10t]

Hi!

Will work on linux only:

Code: Select all

\set tbl '''customer_dimension'''
SELECT export_objects('', :tbl) \g | awk '{ if ($0 ~ /CREATE PROJECTION/) {proj_started=1}; if (proj_started == 1) {print $0} }';

[scutter]

You could also query for the projection names, and then script export_objects() on just the projections.

[becky]

Thanks! Perfect solution provided by the community again! I didn't know that the export_objects function work on projections. I thought it only worked on tables

[id10t]

HI!

export_objects function work on table, projection, view, sequence and user-defined SQL function.

[zxcvmnb]

Hi, related query, default projections mostly have same name as anchor table.
In that select export_objects('', '<schm_name.tabl_name>', false) will always print export object for the table and not the projections.
How do we get export_objects output for projections with same name as a table ?

[JimKnicely]

Can you post an example where the default projection name generated by Vertica is the same as the anchor table name?