extracting a key from a json string having null value

You have many errors in your function. The first one being that you are not returning anything as you are missing a return statement.

Your expression: _data:: json -> 'sdate'::date is not working because the cast operator :: binds more strongly than the ->' operator and thus you are casting ‘sdate’to a date, not the result of the->` operator.

You should also declare the function parameter as json, rather than casting it inside the function. And you need to use ->> to return the value as a text value because there is no direct cast from json to date.

You are casting the result to a date, but your function is declared to return integer which also doesn’t match.

CREATE OR REPLACE FUNCTION public."test"(_data json)
  RETURNS date --<< here
  LANGUAGE plpgsql
  stable
AS 
$BODY$
declare 
  _sdate date := null;
begin
  _sdate := (_data ->> 'sdate')::date;
  return _sdate;
END;
$BODY$;

The way you formatted your code suggests that you think declare is needed for each variable. But declare starts a block that can contain multiple declarations. declarations, it’s not something that needs to be repeated for each variable.

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top