Postgres to-json converts a composite type into empty structure

Question

in the following code example postgresql (9.4) is supposed to transform a local variable of a composite type into a json string. apparently postgresql handle a variable with null value different than a null-value cast to the same type.

in my application i have nested composite types and would like to retrieve null when a variable of a composite type has null as a value.

i hope on of you guys can help me out here.

CREATE TYPE mytype AS (
    id SMALLINT,
    name TEXT
);

DO $$
DECLARE
    l_var mytype;
BEGIN
    l_var := NULL;
    RAISE INFO '%',to_json(NULL::mytype);
    RAISE INFO '%',to_json(l_var) ;
END $$; 

--Output
INFO:  <NULL>
INFO:  {"id":null,"name":null}

Answer 1

yes, PostgreSQL distinguish between ROW with empty fields and NULL. A fix should not be hard - you should to use CASE expression:

postgres=# DO $$
DECLARE
    l_var mytype;
BEGIN
    l_var := NULL;
    RAISE INFO '%',to_json(NULL::mytype);
    RAISE INFO '%',to_json(CASE WHEN l_var IS NULL THEN NULL ELSE l_var END) ;
END $$; 
INFO:  <NULL>
INFO:  <NULL>
DO