ERROR: function json_typeof(json) does not exist

Question

I am getting a problem while running my function in postgres version 9.3.9 It given me a below error when i run this:

Caused by: org.postgresql.util.PSQLException: ERROR: function json_typeof(json) does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.
  Where: PL/pgSQL function myArray(character varying,json) line 9 at SQL statement

Here is my function:

CREATE OR REPLACE FUNCTION myArray(domain_name varchar, val json) RETURNS varchar LANGUAGE plpgsql STABLE AS $$
DECLARE
  result varchar;
  isarray BOOLEAN;
    q cursor for
    select json_agg(blogIn(null,b.value))
    from json_array_elements_text(val) b;
BEGIN
  SELECT json_typeof(val) = 'array' into isarray;
  if not isarray THEN
    return val;
  end if;
  open q;
  fetch q into result;
  close q;
  if result is null then
    return val;
  end if;
  return result;
END;
$$;

It is very strange that this function run without any problem in Postgres 9.5 version but on 9.3 it creating above error. Can someone tell me what actually type cast it require at "SELECT json_typeof(val) = 'array' into isarray;" ?

json_typeof() was introduced in Postgres 9.4, so obviously it is not available in 9.3. Additionally: Postgres 9.3 is no longer supported you should plan an upgrade as soon as possible anyway — user330315
– user330315, Commented Dec 19, 2018 at 9:45
Ahh i see.. is there any alternative of this which i can in Postgres 9.3 ? — user565
– user565, Commented Dec 19, 2018 at 9:47

klin · Accepted Answer · 2018-12-19 10:07:25Z

1

Surely you'll upgrade your Postgres soon. Before this happens you can use this function:

create or replace function is_json_array(json)
returns boolean language sql immutable as $$
    select coalesce(left(regexp_replace($1::text, '\s', '', 'g'), 1) = '[', false)
$$;

answered Dec 19, 2018 at 10:07

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user565 Over a year ago

I run above script by changing a name of function from is_json_array to json_typeof but getting a exception like this; Caused by: org.postgresql.util.PSQLException: ERROR: invalid input syntax for type boolean: "array" Where: PL/pgSQL function myArray(character varying,json) line 9 at SQL statement

klin Over a year ago

This is not json_typeof(). The function returns boolean and checks whether the argument is a json array.

user565 Over a year ago

I am not understanding how this function(is_json_array) would be use as a replacement function of json_typeof in my above function in PostgresSQL 9.3?

IMSoP Over a year ago

@user565 Since you already have a boolean isarray variable, all you should need is SELECT is_json_array(val) into isarray;

Collectives™ on Stack Overflow

ERROR: function json_typeof(json) does not exist

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related