Using AEs to exceed NPS SQL function argument limits

On the NPS, SQL functions are limited to 64 arguments. AEs provide a way to exceed this limit, by using an installed scalar function named NZAEMULTIARG. This function takes up to 64 arguments and returns a varchar string containing a compressed serialization of all the arguments. The AE runtime can deserialize these compressed arguments and make them appear to an AE as standard arguments. NZAEMULTIARG calls can be also nested so that an AE can receive a significantly larger number of arguments. The combined size of all the arguments is limited by the NPS system to 64 K bytes. NZAEMULTIARG can also be used with UDXs.

SELECT * FROM DATA AS t, TABLE WITH
FINAL(my_table_function_ae(NZAEMULTIARG(t.f1, t.f2, t.f3), 100, 200,
NZAEMULTIARG('a', 'b', 'c', 'd'), 100.6));

t.f1, f.f2, t.f3, 100, 200, 'a', 'b', 'c', 'd', 100.6

In this example, my_table_function_ae is registered with argument signature VARARGS, which is the keyword for any number and type of arguments.

NZAEMULTIARG might not work well with shapers since the output of NZAEMULTIARG is never a literal and shapers receive only literals. However, a combination of NZAEMULTIARG and literal arguments can be used to work around this limitation. For more information, see Introduction to shapers and sizers.

myfunction(NZAEMULTIARG(f1, 'abc'), 'constant for Shaper');