Sample Header Ad - 728x90

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