postgresql - Returning Table with null value causes error

Question

I have a simple function that has optional parameters. When I leave out a parameter, which should just default to null, I get an error that it is not an integer.

Here is the function:

CREATE FUNCTION rewrite(_postid integer DEFAULT NULL::integer,
                                       _url character varying DEFAULT NULL::character varying)
    RETURNS TABLE
            (
              PostTypeID                 integer,
              DestinationURL             varchar,
            )
    LANGUAGE plpgsql
AS
$function$
BEGIN
RETURN QUERY
                SELECT 
                NULL AS PostTypeID,
                _url      AS DestinationURL,
                      
                FROM reference.destinations dest1
            
                WHERE length(TRIM(dest1.DestinationURL)) > 0
                AND _url LIKE '%' || TRIM(dest1.DestinationURL)) || '%'
                ORDER BY length(dest1.DestinationURL)) DESC
                LIMIT 1;
END;
$function$

If I run SELECT * FROM utility.rewrite(_url := 'wikipedia.org') then I get this error:

[42804] ERROR: structure of query does not match function result type Detail: Returned type text does not match expected type integer in column 1.

So column1 must be the PostTypeID column in my RETURNS TABLE definition. But I am selecting NULL AS PostTypeID so why is it not just returning NULL?

If I run SELECT * FROM utility.rewrite(_postid = 0, _url := 'wikipedia.org') then it works fine. But I don't want 0 to be returned, I want NULL.

Answer 1

Just because you use the alias posttypeid in the query does not mean that PostgreSQL infers the data type of your PL/pgSQL variable.

Even though NULL can be any data type, PostgreSQL has to determine a data type for the result column of the query. Lacking other information, it arbitrarily chooses text.

Mapping the query result type to the function result type happens later, in PL/pgSQL. That is what causes the error you observe.

You can avoid the problem by specifying the type of NULL with an explicit type cast:

SELECT CAST (NULL AS integer)