7

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?

11

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 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

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:

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:

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

  • 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? – user9645 Dec 5 '18 at 16:23
  • 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. – Erwin Brandstetter Dec 5 '18 at 16:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.