CODEWITHSUNDEEP
postgresql
Dan Collins
Dan Collins

Reputation: 1018

allow variadic unknowns in function arguments

I want to create a small helper function that will allow me to stop repeating this code 100s of times:

SELECT
    jsonb_strip_nulls(
        jsonb_build_object(
            'hello', 'world',
            'value', 5,
            'error', null
        )
    );
-- returns {"hello": "world","value":5}

However, when I try to wrap this in a function I get errors because the string literals are technically unknowns:

CREATE OR REPLACE FUNCTION jsonb_build_nullless_object(VARIADIC anyarray) RETURNS jsonb AS $$
    SELECT
        jsonb_strip_nulls(
            jsonb_build_object(
                VARIADIC $1
            )
        )
$$ LANGUAGE sql IMMUTABLE;

SELECT jsonb_build_nullless_object(
    'hello', 'world',
    'value', 5,
    'error', null
);
-- ERROR:  invalid input syntax for integer: "hello" has type unknown

Since the plain jsonb_build_object handles un-explicitly-typed string literals just fine, I assume there is a function decorator that would allow me to do the same?

Upvotes: 1

Views: 332

Answers (1)

klin
klin

Reputation: 121754

There is no reason to use the pseudo-type anyarray as the arguments always are texts:

CREATE OR REPLACE FUNCTION jsonb_build_nullless_object(VARIADIC text[])
...

Db<>fiddle.

Note that arguments on odd positions are texts, so the whole array of arguments has to be text[]. SQL functions cannot have arguments of type "any" in contrast to some built-in functions like jsonb_build_object(VARIADIC "any").

Upvotes: 2

Related Questions