Support VARIADIC function arguments

[james-johnston-thumbtack]

Is your feature request related to a problem? Please describe.

While looking for a workaround for #14704 to parse ISO 8601 intervals, I thought to try regular expressions to parse an ISO 8601 string myself. This seemed easy enough, especially since my source (Debezium) always outputs the intervals in a very rigid format. A regexp that captures each ISO 8601 interval element, and then uses it in a format string:

PostgreSQL SQL

users=# select format('%s year %s month %s day %s:%s:%s', variadic regexp_match('P1Y2M3DT4H5M6.15S', 'P(\d+)Y(\d+)M(\d+)DT(\d+)H(\d+)M([\d\.]+)S'));
            format
-------------------------------
 1 year 2 month 3 day 4:5:6.15

RisingWave SQL

thumbtack=> select format('%s year %s month %s day %s:%s:%s', variadic regexp_match('P1Y2M3DT4H5M6.15S', 'P(\d+)Y(\d+)M(\d+)DT(\d+)H(\d+)M([\d\.]+)S'));
ERROR:  Failed to run the query

Caused by:
  sql parser error: syntax error at or near variadic at line:1, column:57

It seems that RisingWave does not understand the VARIADIC keyword used by PostgreSQL for unpacking an array into function arguments, as described at https://www.postgresql.org/docs/16/xfunc-sql.html#XFUNC-SQL-VARIADIC-FUNCTIONS

It seems there was a special case recently added for some JSON functions at #13016 but I think it was not generalized for any variadic function support?

Describe the solution you'd like

PostgreSQL-compatible support of the VARIADIC keyword when calling functions with variadic parameters.

Describe alternatives you've considered

I can't think of a cleaner-looking alternative than what PostgreSQL is doing.

For my immediate problem, this, um... finally does the trick.... 😞

thumbtack=> select replace(
                replace(
                    replace(
                        replace(
                            regexp_replace(
                                replace(
                                    replace(
                                        'P1Y2M3DT4H5M6.15S', 'P', ''
                                    ), 'Y', ' year '
                                ), 'M', ' month '
                            ), 'DT', ' day '
                        ), 'H', ':'
                    ), 'M', ':'
                ), 'S', ''
            )::interval;
             replace
----------------------------------
 1 year 2 mons 3 days 04:05:06.15

Additional context

No response

[TennyZhuang]

Thank you for your suggestion. The use case for this feature looks very reasonable, and we will prioritize it and implement it as soon as possible.

[james-johnston-thumbtack]

@TennyZhuang it is not urgent for me, as I did find a workaround. But will be nice if it exists in the future. :)