DarrenW - 8 months ago 150

SQL Question

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, '');
```

Source (Stackoverflow)