Postgresql – How to write a wrapper for a heterogeneous VARIADIC function like jsonb_build_object()

datatypespostgresql

I am just wondering how to write a wrapper function in PostgreSQL (as of 12.x) for a heterogeneous VARIADIC function such as jsonb_build_object?

Most SQL examples of VARIADIC functions I can find are not heterogeneous. The VARIADIC part is essentially an array of elements of the same type. But I need to write a wrapper for jsonb_build_object to do some post-processing. Here, the parameter types in the variadic part (key-value list) are not the same.

Currently, I write:

my_func(jsonb_build_object(k1, v1, k2, v2, ...))

But this boilerplate is becoming boring …

Best Answer

jsonb_build_object() takes VARIADIC "any" as input. The pseudo-type "any" is a placeholder for any input type. Currently, that only works for a C function. The manual:

Functions coded in C (whether built-in or dynamically loaded) can be declared to accept or return any of these pseudo data types. It is up to the function author to ensure that the function will behave safely when a pseudo-type is used as an argument type.

Functions coded in procedural languages can use pseudo-types only as allowed by their implementation languages. At present most procedural languages forbid use of a pseudo-type as an argument type, and allow only void and record as a result type (plus trigger or event_trigger when the function is used as a trigger or event trigger). Some also support polymorphic functions using the types anyelement, anyarray, anynonarray, anyenum, and anyrange.

There is no one-to-one translation for an implementation with another procedural language. There may be workarounds, depending on your exact objective.