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

Question

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?

Answer 1

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().

• user9645: This is great stuff, but I can't seem to get VARIADIC to work in the following test case: SELECT jsonb_build_array('A', VARIADIC array_agg(value)) FROM jsonb_array_elements_text('["B","C"]'::JSONB), any idea why?
• Erwin Brandstetter: You are effectively calling SELECT jsonb_build_array('A', VARIADIC '{B,C}'::text[]), but jsonb_build_array() expects either a list of values, or a single array following the keyword VARIADIC. Depending on what you are trying to achieve, there are various solutions. Please ask a new question about it if anything is still unclear. You can always link to this one for context.