How to use an array as argument to a VARIADIC function in PostgreSQL?
11
votes
1
answer
12232
views
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?
Asked by Jake Feasel
(607 rep)
Dec 13, 2016, 09:30 PM
Last activity: Sep 10, 2021, 01:31 AM
Last activity: Sep 10, 2021, 01:31 AM