DarrenW DarrenW - 2 months ago 30
SQL Question

postgresql - could not determine polymorphic type because input has type "unknown"

I am trying to write a function that checks if an expression is true or not, the function has in total three parameters. The first parameter is the expression, for example 1<5 which would be true.

The second and third parameters let the function know what to return if this statement is true :

CREATE OR REPLACE FUNCTION iff( expression boolean ,true_type anyelement , false_type anyelement )
RETURNS anyelement AS
$$
DECLARE
BEGIN
IF expression = TRUE THEN
RETURN true_type;
ELSEIF expression = FALSE THEN
RETURN false_type;
END IF;
END;
$$
LANGUAGE plpgsql;


The function works for integer values, boolean but not for string literals.

Example:
This statement works and returns 2.

SELECT IFF(false,1,2);


Example 2:
This statement doesnt work and gives the following error message:
could not determine polymorphic type because input has type "unknown"

SELECT IFF(false,'','');

Answer

A string literal in PostgreSQL has type unknown.

You can solve the problem by explicitly casting to the desired type:

SELECT iff(false, ''::text, '');