1

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.

volume one
  • 6,800
  • 13
  • 67
  • 146

1 Answers1

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)
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Wouldn't it make more sense then for the `return table` definition to allow a null value in a column say like `PostTypeID integer default null`? Logically it makes no sense to cast a NULL as an integer. It is absurd. – volume one Jan 12 '21 at 15:10
  • I told you the cause of your problem and how to fix it. If you don't like the way PostgreSQL behaves, that's a different issue. You are welcome to improve it, as long as you don't break backward compatibility. Perhaps I spent too much time with SQL, but I see nothing weird about casting a value. – Laurenz Albe Jan 12 '21 at 15:29
  • Its not that I don't like it how it behaves, I'm trying to understand how it makes sense to convert an unknown entity into an integer when the point of a NULL is that it is unknown? – volume one Jan 12 '21 at 15:32
  • 1
    Right, and after the cast it is an unknown integer. – Laurenz Albe Jan 12 '21 at 17:15
  • Except you did just that in your argument declaration; `_postid integer DEFAULT NULL::integer`. Change to `NULL::integer AS PostTypeID` and you will be matching the input. – Adrian Klaver Jan 12 '21 at 17:15
  • @AdrianKlaver When I made the function I just typed `CREATE FUNCTION rewrite(_postid integer DEFAULT NULL, _url character varying DEFAULT NULL)` without any cast. I think my IDE (IntelliJ Idea/DataGrip) or maybe Postgresql itself added it automatically. I thought that `::` was obsolete and not proper SQL standard anymore? – volume one Jan 12 '21 at 17:40
  • It has never been standard SQL, it is PostgreSQL dialect. – Laurenz Albe Jan 12 '21 at 17:44
  • Other option is to do: `SELECT _postid, _url ...`. Then you would already have a `CAST` NULL. Also the aliases are not necessary, all that matters is that the query output columns match in number an type to the `RETURNS TABLE` declaration. – Adrian Klaver Jan 12 '21 at 20:47