How to use an array as argument to a VARIADIC function in PostgreSQL?

I am trying to make a case-insensitive version of json_extract_path_text(), using the citext module.

I would like this to be a simple wrapper around the built-in function, the only difference being that it accepts citext as the first parameter instead of json. I want this to be a straight pass-through to the native implementation, just with a type conversion beforehand. Here’s what I have so far:

create extension citext;

create or replace function json_extract_path_text ( string citext, variadic params text[]) RETURNS text IMMUTABLE AS
$$
    BEGIN
        SELECT json_extract_path_text(string::json, params);
    END;
$$
LANGUAGE 'plpgsql';

This doesn’t work properly, however, because of the type mis-match:

ERROR:  function json_extract_path_text(json, text[]) does not exist
LINE 1: SELECT json_extract_path_text(string::json, params)
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:  SELECT json_extract_path_text(string::json, params)
CONTEXT:  PL/pgSQL function json_extract_path_text(citext,text[]) line 3 at SQL statement

I’ve tried hacking around a solution using dynamic string construction and EXECUTE, but this is a real hassle and I feel like there must be a cleaner way to pass VARIADIC params through to the inner function. I can’t see any obvious way to do so, however. How can I accomplish that?

Pass the array as is using the VARIADIC key word in the call:

CREATE OR REPLACE FUNCTION json_extract_path_text(string citext, VARIADIC params text[])
  RETURNS text LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
'SELECT json_extract_path_text(string::json, VARIADIC params)';

Call:

SELECT json_extract_path_text('{"f1":{"f2":1,"f3":"foo"}}', VARIADIC '{f1, f3}');

 json_extract_path_text
----------------------
 foo

PARALLEL SAFE for Postgres 9.6 or later.

The manual on VARIADIC:

Sometimes it is useful to be able to pass an already-constructed array
to a variadic function; this is particularly handy when one variadic
function wants to pass on its array parameter to another one. You can
do that by specifying VARIADIC in the call:

SELECT mleast(VARIADIC ARRAY[10, -1, 5, 4.4]);

More details:

• Select rows such that names match elements of input array for pgsql function

To be clear: Inside the function, the VARIADIC parameter is just another array, nothing special about it. VARIADIC being used in two separate function definitions makes the case a bit confusing. The solution is to use the same keyword VARIADIC for a related but different purpose in the function call. Don’t get confused even more.

Aside: Do not quote the language name, it’s an identifier.

Case-insensitive version with citext?

I am trying to make a case-insensitive version of json_extract_path_text(), using the citext module.

While the above function works, it’s not case-insensitive at all. citext preserves original input, which is restored with the cast to text (or json) and can be mixed-case.

I would not use citext to begin with. Various corner case problems:

• Index on column with data type citext not used

For your purpose I suggest calling the built-in json_extract_path_text() with lower(citext_value), which returns lower-case text, and lower-case the 2nd parameter (“path elements”) as well, to make it actually case-insensitive:

SELECT json_extract_path_text(lower('{"F1":{"f2":1,"f3":"foo"}}'::citext)::json
                            , VARIADIC lower('{f1, F3}')::text[]);

Note the cast to text[] after lower().