How to get LIMIT VALUE from projection metadata for TOPK aggregate type?

AlexZH

Hi everyone!

 

Is it possible to get LIMIT VALUE from projection metadata for TOPK aggregate type?

 

I've tried to find this info in v_catalog.projections and v_catalog.projection_columns - but without success.

 

From v_catalog.projections - I got aggregate type - TOPK.

From v_catalog.projection_columns - I got columns in PARTITION BY and ORDER BY clauses.

 

I need to get LIMIT VALUE using SELECT statement (system tables/views) - so I can't use meta-function export_objects() because of error:
[Vertica][VJDBC](6809) ERROR: Inappropriate usage of meta-function ("export_objects")

 

Vertica version:
Vertica Analytic Database v7.2.2-1.

 

Projection example:
CREATE PROJECTION VRT_DWH.PRJ_SMPL_PART_BY_2 (Employee_gender, Courtesy_title, Employee_key) AS
SELECT Employee_gender,
Courtesy_title,
Employee_key
FROM VRT_DWH.TBL_SMPL_ORD_BY
LIMIT 5 OVER (PARTITION BY Employee_gender ORDER BY Courtesy_title DESC);

 

Thanks in advance.
Yours respectfully,
AlexZH

Navin_C

What is the requirement here.

 

Do you want to limit the resultset to 5 values everytime.

Or you want to have 5 records in windows partition clause everytime.

AlexZH

Hi, NC!

 

It's just test example, but main idea is - to have 5 records in windows partition clause everytime.

 

Yours respectfully,
AlexZH

DerekW

I'm not sure if this is stored in the meta data of the projection, but you could get the projection definition using export_objects('','<table_name>') and parse this (e.g. using regex) to retrieve the limit clause.