DarrenW DarrenW - 1 year ago 434
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
IF expression = TRUE THEN
RETURN true_type;
ELSEIF expression = FALSE THEN
RETURN false_type;
LANGUAGE plpgsql;

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

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 Source

A string literal in PostgreSQL has type unknown.

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

SELECT iff(false, ''::text, '');
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download