1

I have migrated my database from postgres version 9.6 to postgres version 13, So I am facing a issue where a function returns different output in both the versions. I am explaining the problem with dummy data because my actual function and type is to much big and complicated.

For example:

Consider there is a users table in public schema, which contains following data:

id name address_line1 address_line2
1 Carlos 4145 Francis Mine, Westwood CA-96137
2 Nathalie 3852 Goldie Lane, Cincinnati OH-45202

My function returns a user defined type. Type definition is as below:

CREATE TYPE user AS 
(
    id                 bigint,
    name               character varying(255),
    address_line1      character varying(255),
    address_line2      character varying(255)
);

The function is as below:

CREATE OR REPLACE FUNCTION get_user(user_id bigint, OUT retval public.user)
    RETURNS public.user
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
    BEGIN
        SELECT 
           *
        INTO
           retval
        FROM
           public.users U
        WHERE
            U.id = user_id
        ;
    END;
$BODY$;

Now when I run the query SELECT get_user(5); so according to function definition value of retval will be SQL NULL (i.e. <NULL>), so in PG v9.6 I got output as "(,,,)" which indicates column with null values. But in PG v13 I got output as <NULL>. I want output of my function in PG v13 similar to the PG v9.6. Because of this when I further pass result of my function get_user into row_to_json() it gives different output.

Output of row_to_json(SELECT get_user(5)); in PG v9.6 will be as below:

{
    "id"            : null,
    "name"          : null,
    "address_line1" : null,
    "address_line2" : null
}

But in case of PG v13 output of row_to_json(SELECT get_user(5)); will be <NULL>.

Jay Godhani
  • 368
  • 2
  • 12
  • If you get a complete NULL value, I would suspect you simply don't have a row with ID = 5 in your table. What does `select * from users where id = 5` give you in the Postgres 14 installation? Btw: your function could be simplified to a `language sql` function. –  Mar 10 '22 at 14:17
  • Thank you for your response but I think you didn't get my question . I want my function to return "(,,,)" instead of returning . Above function is just for example purpose. – Jay Godhani Mar 11 '22 at 08:12

0 Answers0