limiting the number of output rows in UDTF

[sridharsontha]

Hi,

I am a newbie and working on UDF’s . I have a question regarding limiting the number of output rows in an User Defined Transform Function (UDTF)

How to limit the number of rows that need to be output in an UDTF?

Example : select c1,c2, sortK (N,F,c1,c2) over(partition by c1,c2) from tab_sort;

N –> input –> number of rows to output

F–> input –> flag set to A/D to display the output in asc/desc order

I want the above “sortK” UDTF to output top N rows , where input columns c1 and c2 are sorted in ascending/descending order from the table “tab_sort” depending on the input Flag F which is set to A/D respectively. How to do this? Pls help me. Any sample code would be helpful.
My only question is how to limit the number of output rows in an UDTF like I mentioned in my 1st post especially when you specify partition by in an OVER() clause. My requirement is I need to limit the output rows to N ( which is input to UDTF) and I need to specify partition by in an OVER clause. How to go about it. Pls suggest.

Thanks.

[sridharsontha]

Is there a way to get the number of rows read/processed by UDTF?

[JimKnicely]

Hi sridharsontha,

Did you look at the TopKPerPartition example in the /opt/vertica/sdk/examples/TransformFunctions directory?

The first argument in the TopKFactory transform function factory represents a value to limit the number of rows to be returned. This parameter is used in the transform function, specifically here in the code of the TopKPerPartition.cpp file:

Code: Select all

 // If we're already produced num tuples, then break
                if (cnt >= num)
                    break;

P.S. There is a typo in the comment of the example I think it should read "// If we've already ... "